advanced search operators in get_posts (greater than, less than)

[GoogleCodeExporter]

What functionality do you want to see?
I'd love to be able to do the following searches using get_posts:
- find all posts in which the custom field 'course number' is greater than 300
- find all w/ value less than 100
- find all w values between 100 and 200

How hard would it be to implement something like this?

Unrelated: to test a particular svn version of the plugin do I do this:

svn co -r REVNUM http://plugins.vn.wordpres.com/custom-content-type-manager

and then

ln -s custom-content-type-manager/trunk 
wordpress/wp-content/plugins/custom-content-type-manager

? or is there a better way to do it?

Please provide any additional information below.

Original issue reported on code.google.com by mopto...@gmail.com on 2 Dec 2011 at 1:46

[GoogleCodeExporter]

sorry for the noise re: svn, just read the download instructions, which made 
everything clear.  I'm more used to git, so couldn't understand why all of 
these tags & things were in the way of the code I wanted to use... 

Original comment by mopto...@gmail.com on 2 Dec 2011 at 3:17

[GoogleCodeExporter]

No worries re SVN -- took me a while to figure it out too.

That's a good feature request, actually... you could do a work-around by using 
a "date range" search.... not that your custom fields are dates, but the date 
searching does utilize the greater-than/less-than searches.

Try something like this:

$Q = new GetPostsQuery();
$args['date_column'] = 'your_custom_field';
$args['date_min'] = '100';
$args['date_max'] = '200';
$results = $Q->get_posts($args);

I think that will work, but the usage is awkward.  I'm debating how to 
incorporate other types of filters into this, e.g. greater-than, less-than, and 
"LIKE".   One thought is to specify it sorta like an output filter, e.g.

$args['column:<='] = 'Some value';
OR
$args['column:like'] = 'other value';

Would that be too hard to read?  Or too weird to use?  Another option I toyed 
with was this:

$args['column']['>'] = 'other value';

That involves multi-dimensional arrays, which actually makes it somewhat easier 
to program forms for it (e.g. using the GetPostForm class)... it's about as 
easy to read, but harder to type.  Any thoughts?

Thanks for all your reports -- they really help guide the dev.

Original comment by fireproofsocks on 2 Dec 2011 at 4:31

• Changed state: Accepted

[GoogleCodeExporter]

so cool to see you trying to develop this.  

I think the multi-dimensional syntax is slightly easier to read -- as you say, 
a touch harder to type, but if it makes the programming more manageable then I 
say go for it.  The difference between the two is minimal from my perspective.  
Is there a performance difference, do you think? I'd consider that first.

I'm not sur the date_column workaround works.  I have the following code:

  $Q = new GetPostsQuery();
  $args = array();
  $args['post_type'] = 'courses';
  $args['orderby'] = 'course_number';
  $args['date_column'] = 'course_number';
  $args['date_min'] = '300';
  $args['date_max'] = '500';
  $args['order'] = 'ASC';
  $args['post_status'] = 'publish';
  $courses = $Q->get_posts($args);

  foreach ($courses as $c){
    $n = $c['course_number'];
}

which generates the top part of this page:
http://department-test-wp.hackinghistory.ca/course-type/courses/

(you can see that the undergrad who input all my course numbers didn't do what 
I expected -- I wanted the integer number in its own field, so it could be 
sorted on, and instead I have some integers in that field, and some strings.) 
I'm not sure if that's part of the issue, or whether date strings need to be in 
a special format...  

anyway I htink this is a great idea and I look forward to the implementation. 

Original comment by mopto...@gmail.com on 3 Dec 2011 at 3:45

[GoogleCodeExporter]

Yeah, that's gonna sort alphabetically, not numerically, which makes things 
weird... but it does look like it's working.

The big reason for the multi-dimensional syntax is that it's easier to isolate 
the operator, e.g. into its own form element.  So you could have a search form 
with a text field for the term and a drop-down for the operator, for example.

Original comment by fireproofsocks on 3 Dec 2011 at 4:38

[GoogleCodeExporter]

the alphabetical sort is working, but I think the date_column limit isn't 
actually filtering out posts that don't meet the date_min and date_max 
criterion.  so, e.g., "1006" is the first course_number to be printed out in 
that link in my post above.  I imagine this could be because these are being 
treated as date objects, not numbers; I'm not sure how the date parsing 
functions treat integers in PHP.  

Original comment by mopto...@gmail.com on 3 Dec 2011 at 11:17

[GoogleCodeExporter]

ps, i see what you mean about hte form -- that sounds smart.

Original comment by mopto...@gmail.com on 3 Dec 2011 at 11:19

[GoogleCodeExporter]

Can you do this and paste the resulting SQL query?

$Q = new GetPostsQuery();
$args['...'] = '...';
// stuff
$results = $Q->get_posts($args);
// ...
$Q->debug();  // <-- this will print out all softs of crazy stuff, including 
the raw SQL query.

Original comment by fireproofsocks on 3 Dec 2011 at 11:53

[GoogleCodeExporter]

wow, that is a lot of stuff.  I'm not sure what to paste in, but the full
results are at the top of this page:

http://department-test-wp.hackinghistory.ca/course-type/courses/

it doesn't look like the date_min and date_max args are being honored.
This is my actual code:
 $Q = new GetPostsQuery();
  $args = array();
  $args['post_type'] = 'courses';
  $args['orderby'] = 'course_number';
  $args['date_column'] = 'course_number';
  $args['date_min'] = '300';
  $args['date_max'] = '500';

  $args['order'] = 'ASC';
  $args['post_status'] = 'publish';
  $courses = $Q->get_posts($args);
  print_r($Q->debug());

Original comment by mopto...@gmail.com on 4 Dec 2011 at 3:12

[GoogleCodeExporter]

Ok, that's good to see.  I'ma gonna paste that in here down below.   We can see 
that there are some errors... so it looks like my date-filtering doesn't like 
using raw numbers as date arguments.  So... I have to fix up some stuff to do 
the greater-than/less-than arguments... 

But also, I can see that your SQL settings aren't allowing for enough memory 
usage to concatenate the custom fields.  You need to go into 
include/SummarizePosts.php file and bump the following setting to something 
larger:

public static $group_concat_max_len = 4096;

Here's the gruesome details:

Errors
Invalid datetime argument: :300
Invalid datetime argument: :500
There was a problem accessing custom fields. Try increasing the 
group_concat_max_len setting.
Warnings
Search parameters ignored: search_term and search_columns must be set.
Notices
orderby column not a default post column: course_number
Execution Time
0.38 seconds
Arguments
For more information on how to use this function, see the documentation for the 
GetPostsQuery::get_posts() function.

limit: --
offset: --
orderby: course_number
order: ASC
include: --
exclude: --
append: --
meta_key: --
meta_value: --
post_type: Array (courses)
omit_post_type: Array (revision, nav_menu_item, fa_slider)
post_mime_type: --
post_parent: --
post_status: Array (publish)
post_title: --
author: --
post_date: --
post_modified: --
yearmonth: --
date_min: --
date_max: --
date_format: --
taxonomy: --
taxonomy_term: --
taxonomy_slug: --
taxonomy_depth: 1
search_term: --
search_columns: Array (post_title, post_content)
join_rule: AND
match_rule: contains
date_column: course_number
paginate: false

SELECT wp_posts.* , parent.ID as 'parent_ID' , parent.post_title as 
'parent_title' , parent.post_excerpt as 'parent_excerpt' , author.display_name 
as 'author' , thumbnail.ID as 'thumbnail_id' , thumbnail.guid as 
'thumbnail_src' , metatable.metadata , orderbymeta.meta_value as metasortcolumn 
FROM wp_posts LEFT JOIN wp_posts parent ON wp_posts.post_parent=parent.ID LEFT 
JOIN wp_users author ON wp_posts.post_author=author.ID LEFT JOIN 
wp_term_relationships ON wp_posts.ID=wp_term_relationships.object_id LEFT JOIN 
wp_term_taxonomy ON 
wp_term_taxonomy.term_taxonomy_id=wp_term_relationships.term_taxonomy_id LEFT 
JOIN wp_terms ON wp_terms.term_id=wp_term_taxonomy.term_id LEFT JOIN 
wp_postmeta thumb_join ON wp_posts.ID=thumb_join.post_id AND 
thumb_join.meta_key='_thumbnail_id' LEFT JOIN wp_posts thumbnail ON 
thumbnail.ID=thumb_join.meta_value LEFT JOIN wp_postmeta ON 
wp_posts.ID=wp_postmeta.post_id LEFT JOIN ( SELECT wp_postmeta.post_id, CONCAT( 
GROUP_CONCAT( CONCAT(wp_postmeta.meta_key,'::::', wp_postmeta.meta_value) 
SEPARATOR ',,,,'), '$$$$') as metadata FROM wp_postmeta WHERE 
wp_postmeta.meta_key NOT LIKE '\_%' GROUP BY wp_postmeta.post_id ) metatable ON 
wp_posts.ID=metatable.post_id LEFT JOIN wp_postmeta orderbymeta ON 
wp_posts.ID=orderbymeta.post_id AND orderbymeta.meta_key = 'course_number' 
WHERE ( 1 AND wp_posts.post_type NOT IN 
('revision','nav_menu_item','fa_slider') AND wp_posts.post_type IN ('courses') 
AND wp_posts.post_status IN ('publish') ) GROUP BY wp_posts.ID ORDER BY 
metasortcolumn ASC 

Original comment by fireproofsocks on 4 Dec 2011 at 3:19

[GoogleCodeExporter]

duh, the errors are reported right at the top:

Errors

   - Invalid datetime argument: :300
   - Invalid datetime argument: :500
   - There was a problem accessing custom fields. Try increasing the
   group_concat_max_len setting.

not sure where the ":" comes from, but I guess integers aren't accepted as
datetime objects (too simple a notation?).

Original comment by mopto...@gmail.com on 4 Dec 2011 at 3:23

[GoogleCodeExporter]

oops, sorry, posts crossed. I tried increasing group_concat_max_len to
163840000 but that didn't get rid of the error message; maybe some other
kind of problem? Unfortunately GetPostsQuery.php is a bit beyond me, looked
in there but can't see what the proble might be.

Original comment by mopto...@gmail.com on 4 Dec 2011 at 3:34

[GoogleCodeExporter]

yeah, you're not going to be able to filter posts using the data work-around 
like I had hoped.  God, don't put a huge number in there like that... MySQL 
probably will ignore it.  Try doubling it, but it won't make your query work.  
It's just saying that it can't grab all the custom fields in one swoop.

Original comment by fireproofsocks on 4 Dec 2011 at 3:37

[GoogleCodeExporter]

Your best bet here might be to write your own custom query for this right now 
-- I'm trying to get CCTM 0.9.5 out the door and I won't be able to work on the 
Summarize Posts component for a while.

Original comment by fireproofsocks on 4 Dec 2011 at 3:41

[GoogleCodeExporter]

(1) group_concat_max_len -- I tried 8192, then 16384, then 16384000.  None
of them got rid of the error...
(2) I was also thinking I'd need to write my own query for this, though I
can also just filter the query results afterwards. I need to buy your book
though! I am realizing I should figure out how to write my own shortcodes.
sigh. this is getting more complicated all the time...

psyched for 0.9.5!  -- m

Original comment by mopto...@gmail.com on 4 Dec 2011 at 4:27

[GoogleCodeExporter]

Man... I'm worried if Summarize Posts mis-behaves on different servers. Thanks 
for your feedback here.  I have a lot of notes for the input syntax of this, 
but I have to rework the Summarize Posts plugin a lot to make that stuff 
work... it'll be cool once it's working though... heehee.

If you're interested, I just published another plugin for writing your own 
shortcodes and adding your own PHP to pages: 
http://wordpress.org/extend/plugins/php-snippets/

Still in beta, but the gist is you can upload your own PHP files to a custom 
directory (currently, only the default wp-content/plugins/php-snippets/snippets 
directory is supported), and put in any PHP file in there (just use a 
.snippet.php suffix), and you can use a TinyMCE button to select your script.  
I need to document it, but it's working as of today.

Original comment by fireproofsocks on 4 Dec 2011 at 4:48

[GoogleCodeExporter]

wow, that new shortcode plugin is great.  is it possible to pass parameters
to the snippet, e.g.:

[my-get-courses-snippet order="ASC"  min_num="100" max_num="199"]?
sorry to keep asking, my php isn't quite good enough to derive the answer
directly from the code.

Original comment by mopto...@gmail.com on 4 Dec 2011 at 4:39

[GoogleCodeExporter]

I need to integrate that... not sure the best way, since WP has some functions 
designed to filter input parameters:
http://codex.wordpress.org/Function_Reference/shortcode_atts

Your can get any arguments passed to the shortcode currently by doing this:

extract($args[0]);

E.g. if you called in via [my-snippet order=ASC] would make the variable 
"$order" available in your snippet.

Original comment by fireproofsocks on 4 Dec 2011 at 6:42

[GoogleCodeExporter]

ah, that's going to be good enough for me.  thx everett.

Original comment by mopto...@gmail.com on 5 Dec 2011 at 5:49

[GoogleCodeExporter]

I started adding these controls to GetPostsQuery.  Getting a web form that can 
feed this to the class is going to be trickier...

Original comment by ever...@fireproofsocks.com on 26 Jan 2012 at 4:58

• Changed state: InProgress

[GoogleCodeExporter]

Original comment by ever...@fireproofsocks.com on 2 Mar 2012 at 12:10

• Added labels: Milestone-Release0.9.6

[GoogleCodeExporter]

Ok, there's a version of this now on the dev branch: Committed revision 513322.

I need to reconcile the special behavior that modifies built-in columns in 
wp_posts, e.g. comma-separated lists to array for post_type and post ID etc.

Original comment by ever...@fireproofsocks.com on 2 Mar 2012 at 3:51

• Changed state: Fixed

[GoogleCodeExporter]

E.g. here's some sample code:

$Q = new GetPostsQuery();
$args = array();
$args['post_title']['starts_with'] = 'S';
$results = $Q->get_posts($args);

But you can't currently do the extended operators with columns that already 
have special behavior defined, e.g. post_type.  Still thinking where to make 
that cut...

Original comment by ever...@fireproofsocks.com on 2 Mar 2012 at 3:57