Unfortunately, you’re not going to be able to do this with the search_terms parameter. Our query class simply isn’t set up to work that way.
You may be interested in looking at my plugin BP Better Directories, which takes probably the only viable route to doing what you want to do: it filters the SQL query directly, and then adds more WHERE clauses. Here’s the plugin https://github.com/boonebgorges/bp-better-directories and here are some of the key parts that you will want to look it. (Note that I’ve tried to be super abstract in order to handle different kinds of content – your code could be much simpler.)
– where I add the filters to the queries: https://github.com/boonebgorges/bp-better-directories/blob/0.9.x/bpbd.php#L51
– the heavy lifting is https://github.com/boonebgorges/bp-better-directories/blob/0.9.x/bpbd.php#L104 It’s written to loop through and do an additional join for each of the search parameters. Line 127 is where I’m making the new LIKE clauses. Then, on 142, I blow up the original query and stick my new clauses in there.
Not pretty (and it won’t scale infinitely – don’t try doing this with 10 different filters on a large member database!) but it does work.
Thanks @boonebgorges I appreciate the help. Going in to BP, I thought it would be a simple function to filter members. You said this won’t scale infinitely so would you foresee an issue with a site having 300 members where I’d want to display 10 filtered lists on a page? For instance, a list broken up into 10 categories? I’ll be diving in to this over the weekend but first wondered if this is a safe path to travel or if this won’t work for me. Thanks again!
The core reason why it’s difficult to do infinite filters like this is because BP xprofile fields are abstracted. That is to say, in order for our profile fields to be extensible (so that your site can have different profile fields from mine) we have moved the profile data to a separate table, which is essentially a key-value table. This is in contrast to a solution where you store all profile data in a single table, making it very easy and fast to filter by many criteria at once (the trade-off being that the table structure needs to be set in stone).
The numbers you cite shouldn’t be much of a problem. You may experience some slowness if you try filtering along 7, 8, 9 criteria at the same time, though I suspect that this is probably an edge case. In any case, some solid MySQL query caching will help.
Anyone have a solution to converting the vertical xprofile field database structure to a horizontal more efficient structure?
I have 10 million entries in my xprofile_data table and my site crashes everytime i try to filter anything with ajax… very frustrting.
i am going to extract the important xprofile fields and convert them to a horizontal database structure and point my search box to the new table.
anyone done this before?
We’d be interested in seeing your slow query log from MySQL, and any other hard numbers relating to how well xprofile scales with 10 million entries. It’d be awesome if you could create a ticket on https://buddypress.trac.wordpress.org or send me those details to paul/buddypress/org.
Sure I’d be happy to provide that. Does anyone have a suggestion for mySQL query or PHP script to copy the xprofile tables over to a new normalized table?
Normalized but most importantly TRANSPOSED.
