Query for Searching Post through Custom Fields

Hi,

Within my site, I had to enter a lot of Custom Fields within every post to maximize the functionality. The data is big, so a search is also needed which is customized to search only through Custom Fields.

I have entered the data, made the query and it runs somewhat fine too, but only problem is that it shows the records which shouldn’t match the parameters of the Query I am providing.

I will very briefly describe the both tables that I am using in my query, the data they have and what I am sending through query.

I have two tables wpposts and wppostsmeta.

The Structure of wpposts and wppostsmeta is as follows:-

wpposts:-

• ID
• post_title

and

wppostsmeta

• ID
• post_id
• meta_key
• meta_value

The Data which these tables have at the moment is:-

wpposts Records.

ID | post_title
__________________

174 | Clean Energy through Water
238 | Water Saving Initiative
191 | Water Sustainable Initiative
224 | Water Irrigation in Remote Areas

wppostsmeta Records.

ID | post_id | meta_key | meta_value
_____________________________________________

812 | 174 | countryi | Belgium
699 | 191 | countryi | Belarus
937 | 224 | countryi | Belgium
909 | 238 | countryi | Netherlands

811 | 174 | countryb | France
671 | 191 | countryb | Belarus
698 | 191 | countryb | Belarus
936 | 224 | countryb | France
908 | 238 | countryb | Netherlands

933 | 224 | activity | Capacity development
905 | 238 | activity | Communication

809 | 174 | audience | a:3:{i:0;s:24:”Educational institutions”;i:1;s:25:”Professional organization”;i:2;s:30:”Small and Medium sized company”;}

669 | 191 | audience | a:3:{i:0;s:14:”General public”;i:1;s:15:”Local authority”;i:2;s:15:”Local community”;}

696 | 191 | audience | a:3:{i:0;s:14:”General public”;i:1;s:15:”Local authority”;i:2;s:15:”Local community”;}

934 | 224 | audience | a:4:{i:0;s:14:”General public”;i:1;s:24:”Educational institutions”;i:2;s:15:”Local authority”;i:3;s:15:”Local community”;}

906 | 238 | audience | a:5:{i:0;s:14:”General public”;i:1;s:24:”Educational institutions”;i:2;s:16:”Large enterprise”;i:3;s:15:”Local authority”;i:4;s:15:”Local community”;}

806 | 174 | focusarea | a:4:{i:0;s:12:”Biodiversity”;i:1;s:15:”Desertification”;i:2;s:21:”Drinking water supply”;i:3;s:6:”Energy”;}

659 | 191 | focusarea | a:3:{i:0;s:21:”Drinking water supply”;i:1;s:20:”Freshwater ecosystem”;i:2;s:11:”Groundwater”;}

693 | 191 | focusarea | a:3:{i:0;s:21:”Drinking water supply”;i:1;s:20:”Freshwater ecosystem”;i:2;s:11:”Groundwater”;}

930 | 224 | focusarea | a:5:{i:0;s:11:”Agriculture”;i:1;s:16:”Flood management”;i:2;s:10:”Irrigation”;i:3;s:17:”Land use planning”;i:4;s:10:”Sanitation”;}

902 | 238 | focusarea | a:4:{i:0;s:12:”Biodiversity”;i:1;s:15:”Desertification”;i:2;s:19:”Disaster prevention”;i:3;s:6:”Energy”;}

The Records in the fields are straight forward, except for the meta_key focusarea and audience. This is a list_item stored with multiple values. these are just stored in a weird way.

My Query:-

Now, to my point. As you can see now that for every record in wpposts, we have multiple records in wppostsmeta; what we will be looking for is to get (one or more) records from wpposts table based on the values in wppostsmeta.

For example, which post has a

• focusarea = Irrigation
• activity =Capacity Development
• audience =General Public
• status = In development
• countryb = Any
• countryi = Any

or another example could be to have focusarea = Energy and rest same as above.

Here comes the Problem. The focusarea has a value of Irrigation in only one post. That is with the Id:- 224 and with the title Water Irrigation in Remote Areas. This is visible in the row with wppostsmeta ID as 930. Hence my query should show one record but in contrary it shows all four post.

Query is:-

SELECT DISTINCT (ID), wposts . * , wpostmeta . * 

FROM aq_posts wposts, 

aq_term_taxonomy iterm, aq_term_relationships irelation, aq_terms terms,

aq_postmeta wpostmeta, aq_postmeta wpostmeta2, aq_postmeta wpostmeta3, aq_postmeta wpostmeta4, aq_postmeta wpostmeta5, aq_postmeta wpostmeta6

WHERE 

wposts.ID = wpostmeta.post_id

AND wpostmeta3.meta_key =  'focusarea'
AND wpostmeta3.meta_value LIKE  '%Irrigation%'
AND wpostmeta4.meta_key =  'activity'
AND wpostmeta4.meta_value =  'Capacity development'
AND wpostmeta5.meta_key =  'status'
AND wpostmeta5.meta_value = 'In development'
AND wpostmeta6.meta_key =  'audience'
AND wpostmeta6.meta_value LIKE '%General public%'

AND wposts.ID = irelation.object_id
AND irelation.term_taxonomy_id = iterm.term_taxonomy_id
AND terms.term_id = iterm.term_id
AND terms.slug='projects'
AND iterm.taxonomy = 'category'

AND wposts.post_status =  'publish'
AND wposts.post_type =  'post'

GROUP BY wposts.post_title

The Query first finds the listings matching the meta keys, then it further filters any other record not from the cateogry of “projects” and then it has last two clauses to only show published posts.

The Query is taking TOO long, and just returns all the records in the wposts table (all 4) …. ?? and not the correct ones. It should show one, right?

and it is taking super long … how to fix this …

please help, I will be very thankful to you.