Support

Account

Home Forums General Issues complex query between objects

Unread

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 :

    a:2:{i:0;s:1:”4″;i:1;s:1:”6″;}

    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.

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.

We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Cookie Policy. If you continue to use this site, you consent to our use of cookies.