Custom MySQL and Related Tables Question

  • We need to sort a query based on a custom taxonomy. The problem is that we can’t just sort on the field alone, because it has multiple values associated with it. We were thinking of using PHP to pull the query, isolate the value from the custom taxonomy that we need, put that value as well as the other data we pulled into an array, sort the array, and return back the now sorted data. We are now thinking the more effective way is to actually use a related database table, since that is how the data should be in the first place. Is this possible? If so, how do you use two tables in a WP query?

Viewing 3 replies - 1 through 3 (of 3 total)

  • Can you give a real example of what you are trying to do?

    Thread Starter cbush57

    (@cbush57)

    Sure thing.

    Basically we have a product shelf that members can add stuff to. When a product is added to a member’s shelf, it shows on their personal shelf, but also on the public community shelf.

    The issue is that when a member adds a product, it is stamped with a publish date and shows appropriately on their shelf according to the order they added it. However if a second member adds that same product, then the publish date gets renewed and it changes the order of the first members shelf.

    What we want to do, is basically have an additional field attached to a product that is a member specific sequence number. The problem is that right now, since the shelf shares the same data as the community shelf, every member’s sequence code gets added as a value under the “Sequence” field. For example, a “Book 1” can have a member sequence value of “123_01, 143_14, 154_02, etc” where the first part of that number is the member id “123_” and the second part is the sequence to appear on their personal shelves. We can use PHP to separate out the values, then populate all the data back into an array, sort, then return it in a new order, but that seems likes a work around and not a long term solution. Ideally we want to create another simple table that would store and relate the sequence information. We done that on other databases we’ve built from scratch, but not with WordPress. I hope that helps.

    Thanks.

    I don’t believe that you need a separate table, but I need more detail about the code you use to select the records for an individual member.

    For example, suppose the member number is in a variable named $member. Then a query similar to this should get the records in the proper order (UNTESTED):

    $sql = " SELECT p.* FROM $wpdb->posts p
JOIN $wpdb->postmeta pm ON (p.ID = pm.post_id)
WHERE pm.meta_key = 'Sequence'
AND pm.meta_value LIKE '$member%'
ORDER BY pm.meta_value";

    If you want to stick with query_posts, the same thing can be accomplished using filters to alter the query.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Custom MySQL and Related Tables Question’ is closed to new replies.