[wp-hackers] ordering by a custom field

[Brian Fidler]

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
>