[wp-hackers] ordering by a custom field

[Brian Fidler]

Thanks Mike,

I think I left out my main bullet point.


   - I have a second custom field in my posts that allows me to set
   display_order. Instead of ending my query with ORDER BY
   wp_posts.post_title ASC, I'd like to order by the custom field
   "display_order"


and secondly, is there a simpler way to run this query using wpdb class?

thanks
brian


On Wed, May 12, 2010 at 8:56 PM, Mike Schinkel
<mikeschinkel at newclarity.net>wrote:

> 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
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>