How can I use a WHERE statement in my template? 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 &amp; 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!
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'],
}) %}