Home Forums General Issues complex query between objects


complex query between objects

  • Hello,

    I’ve been trying to achieve something and I can’t figure out how to do…

    First of all let me explain what I have :

    – I have a custom post type call student, with a post_title (the student full name) and an objet called “parent”, of type user

    – I have my users who have custom fields like firstname, lastname, address, etc.

    What I need to do is : search for all the students whose name contains a given string OR their parent’s name.

    I tried to use get_posts to achive that but I didn’t succeed.
    So I tried with wpdb, thinking I could write the sql query more easily but that’s not the case…

    For now I have :

    query 1:
    SELECT * FROM wp_posts WHERE
    post_status LIKE ‘publish’
    AND post_type LIKE ‘student’
    AND post_title LIKE ‘%$searched_string’

    It’s working fine, it was not the hardest part 🙂

    On the other hand I tried to get all the users whose first of lastname contains the string, so I have this query :

    query 2:
    SELECT * FROM wp_users u
    INNER JOIN wp_usermeta um
    ON um.user_id = u.ID
    WHERE um.meta_key = ‘first_name’
    AND um.meta_value LIKE ‘%searched_string%’
    OR um.meta_key = ‘last_name’
    AND um.meta_value LIKE ‘%searched_string%’

    This gives me the list of parents, no problem neither.

    Now I need to get the students related to these parents, and I don’t know how to do because parents id are stored into post_meta table, like this :


    Here the students has 2 related parents, of IDs 4 and 6.
    I have no way to do something like :

    SELECT * FROM wp_posts WHERE
    post_status LIKE ‘publish’
    AND post_type LIKE ‘student’
    AND post_title LIKE ‘%$searched_string’
    OR parent_id IN (4, 3)

    In this example I could get parents IDs with query 2, and then construct query 1 to say that student’s parents ID have to be one of the IDs found.

    I don’t know if I’m clear but what I know is I’m stuck.

    Any idea of how I could achive that ?

    Thanks a lot.

  • Hello, anyone to help me ?
    I’m very stuck…

Viewing 2 posts - 1 through 2 (of 2 total)

The topic ‘complex query between objects’ is closed to new replies.