How can I use a WHERE statement in my template?

Question

I am trying query against URL segments. Where I am hung up is how to pass those arguments to a related field.

For example, here is my current query:

twig

{% set profiles = craft.entries({
    section: 'profiles',
    with: ['userAccount']
}) %}

userAccount is a user field that is related back to the user.

I get all entries along with the user account information & all is well.

I would like to query against the userAcount field to only return the appropriate entries in the profile section given url segments.

For example: www.mysite.com/profiles/foo/2017

From the userAccount field:

foo is a checkbox field (name is userOptions. Options are: foo, bar, baz)

`2017 is a plain text field.

This is what I am trying to accomplish (obviously is not correct)

{% set profiles = craft.entries({
    section: 'profiles',
    with: ['userAccount'],
    where: ['userAccount[0].year', '=', craft.request.segment('3')],
    where: ['userAccount[0].userOptions', '=', craft.request.segment('2')],
   limit: null
}) %}

I am getting entries bak when I only query against the year. I haven't checked if they are correct but at least I get something. Where I'm thrown is since userOptions could be any value, how can I check that field if a selected option matches the url segment? Any user could have foo and bar selected. I think I need something like a WHERE IN or something...

Thank you for any suggestions!

Answer 1

You would first need to query for the users that match your criteria and than query for the profile entries which are related to one of these users.

{% set userIds = craft.users({
    year: craft.request.getSegment(3),
    userOptions: craft.request.getSegment(2),
}).ids() %}

{% set profiles = craft.entries({
    section: 'profiles',
    relatedTo: {
        targetElement: userIds,
        field: 'userAccount',
    },
    with: ['userAccount'],
}) %}