[wp-hackers] ordering by a custom field

[Mike Schinkel]

Hi Brian,

Reading your email it sounds like you answered your own question?  I did a lot with NextGen Gallery and SQL on a project, if you can clarify your question I'm sure I can help.

-Mike

On May 12, 2010, at 11:15 PM, Brian Fidler wrote:

> Thanks guys,
> 
> So I clearly need to become better acquainted with the wpdb class but the
> documentation is a little sparse for a challenging database query. How could
> I set up the following query using wpdb?
> 
> $pageposts = $wpdb->get_results( "
>   SELECT wp_posts.ID,
>      wp_posts.post_title,
>      wp_posts.post_name AS post_permalink,
> 
>      wp_postmeta.meta_key,
>      wp_postmeta.meta_value,
> 
>      wp_term_relationships.term_taxonomy_id,
> 
>      wp_ngg_pictures.filename,
>      wp_ngg_pictures.galleryid AS galleryid,
>      wp_ngg_pictures.pid,
> 
>      wp_ngg_gallery.name AS gallery_name
> 
>    FROM wp_posts
>      INNER JOIN wp_term_relationships ON wp_term_relationships.object_id =
> wp_posts.ID
>      INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id,
> wp_ngg_gallery
>      INNER JOIN wp_ngg_pictures ON wp_ngg_gallery.gid =
> wp_ngg_pictures.galleryid
> 
>   WHERE wp_term_relationships.term_taxonomy_id = '$myCategoryID'
>      AND wp_postmeta.meta_key = 'gallery_id'
>      AND wp_postmeta.meta_key = 'gallery_id'
>      AND ( wp_postmeta.meta_value = wp_ngg_pictures.pid )
>   ORDER BY wp_posts.post_title ASC
> " );
> 
> 
> I know that's really confusing so here is the essence of what I'm trying to
> do...
> 
>   - I am using NextGen Gallery to upload different galleries of images.
>   NextGen assigns each image a Gallery ID (wp_ngg_pictures.galleryid).
>   - I've set up a custom field for my posts called "gallery_id" and in this
>   field I put the NextGen Gallery ID.
>   - I am using this query to generate a list of thumbnails of all images
>   that are in the posts' custom field from a specific category, so I end up
>   with a grid of images from my posts in category X rather than the grid
>   generated from NextGen gallery. For example I have a
>   category titled "paintings" and a category titled "prints". When a visitor
>   views a post in the category "paintings" they will see a grid of thumbnails
>   built from all other posts' that are in the category "paintings"
> 
> 
> 
> On Wed, May 12, 2010 at 2:55 PM, Simon Blackbourn <piemanek at gmail.com>wrote:
> 
>>> En/na Brian Fidler ha escrit:
>>> 
>>>> Thanks, I'm using a custom query because I'm also using the NextGen
>>>> Gallery
>>>> 
>>>> and I need to query based on both NGG and custom fields.
>>>> 
>>>> Is there a way to access data from NGG with $wp_query, I wasn't able to
>>>> find
>>>> anything.
>>>> 
>>>> 
>> 
>> You can use $wpdb->get_results to retrieve data from nextgen gallery tables
>> (as in fact you can from tables created by any WordPress plugin).
>> 
>> The best way to figure out how is to look in nggfunctions.php, search for
>> SELECT and see how the queries are constructed in there.
>> 
>> Use PHPmyAdmin to see the tables, fields and datatypes (nextgen gallery
>> creates three tables I believe).
>> 
>> Simon
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>> 
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers