custom field search query problem

I want to select items by specific custom field values. It seems to work almost, but just not the way it should:

demo on http://www.saulmade.nl/gallery2/main.php?g2_view=customfieldsearch.CustomSearchScan (I'm fidling with the code so rubbish could be dumped on the screen and I could be switching between AND or OR)

This is an 'AND search of custom field values'

SELECT DISTINCT([CustomFieldMap::itemId]), [CustomFieldMap::field], [CustomFieldMap::value] FROM [CustomFieldMap], [GalleryAccessSubscriberMap] WHERE [CustomFieldMap::itemId] = [GalleryAccessSubscriberMap::itemId] AND(([CustomFieldMap::field] LIKE 'MediaType' AND [CustomFieldMap::value] LIKE 'Image') AND ([CustomFieldMap::field] LIKE 'Country' AND [CustomFieldMap::value] LIKE 'NL')) AND [GalleryAccessSubscriberMap::accessListId] IN (?,?,?,?) ORDER BY [CustomFieldMap::itemId] DESC

testcase: mediatype=Image + Country=NL
-> should return http://www.saulmade.nl/gallery2/main.php?g2_itemId=867 and http://www.saulmade.nl/gallery2/main.php?g2_itemId=1892
but returns nothing
testcase: mediatype=Image
-> returns the right images
testcase: Country=NL
-> returns the rigth images

OR search of custom field values

SELECT DISTINCT([CustomFieldMap::itemId]), [CustomFieldMap::field], [CustomFieldMap::value] FROM [CustomFieldMap], [GalleryAccessSubscriberMap] WHERE [CustomFieldMap::itemId] = [GalleryAccessSubscriberMap::itemId] AND(([CustomFieldMap::field] LIKE 'MediaType' AND [CustomFieldMap::value] LIKE 'Image') OR ([CustomFieldMap::field] LIKE 'Country' AND [CustomFieldMap::value] LIKE 'NL')) AND [GalleryAccessSubscriberMap::accessListId] IN (?,?,?,?) ORDER BY [CustomFieldMap::itemId] DESC

testcase: mediatype=Image + Country=NL
-> should return http://www.saulmade.nl/gallery2/main.php?g2_itemId=867 and http://www.saulmade.nl/gallery2/main.php?g2_itemId=1892
but returns duplicate values (one time matching on MediaType and a second one on Country = 2 matches for the same item = 4 results)
testcase: mediatype=Image
-> returns the right images
testcase: Country=NL
-> returns the rigth images

I believe I should do domething with a join, but I can't wrap my mind around it.

Please help

Hey Termite,

I saw in my logs that you tried to help me and I have sent you a note on the channel.
Thanks and thanks for coming here and help me again!

I do understand why it doesn't work, but getting it to work the way I want is something else

I had tried your suggestion before, but the problem with that query is that I can't do a AND search (several customfields must match) but only a OR search. I could do a seperate query for each custom field keyword and then intersect or merge or whatever the results, but I believe this shouldn't be that hard to do with a single query :-/

Hey it's indeed for iEARN, how do you know?! You don't happen to be 'Erfaan' do you?

Thanks,
Paul

and when I have more custom fields?
a query per customfield?

Should you be able to work out a query using subqueries, please do

I believe I have made something working in the meantime. I will post it when it proves to be working after testing.

Thanks

thanks, that was what I had in mind when I said 'I believe I should do domething with a join, but I can't wrap my mind around it.'...

I know subqueries don't work in older mySQL versions, but this is no subquery is it? I believe it's called a self join.

SO I have several options now:
- your query
- a query for each custom field
- a OR search with all custom field values and then when all custom fields should match, check on how many cutom fields for each result matched.

The query for the last option would be the 'OR search of custom field values' query in my initial post and would use this code to return all results on OR and only the items with all searched custom fields on AND. (Both results would hold all the custom field details that were found for each item.)

	if ($searchType == "AND") // AND
	{
		$fieldsPerItems = array();
		while ($result = $results->nextResult()) {
			$fieldsPerItems[(int)$result[0]][] = array('key' => $result[1], 'value' => $result[2]);
		}

		$searchResults = array();
		foreach ($fieldsPerItems as $itemId => &$fieldsPerItem){
			if (count($fieldsPerItem) >= $numOfCriteria)
			{
				$searchResults[] = array('itemId' =>$itemId, 'fields' => $fieldsPerItem);
			}
		}
	}
	else // OR
	{
		$searchResults = array();
		while ($result = $results->nextResult()) {
			$searchResults[(int)$result[0]]['itemId'] = (int)$result[0];
			$searchResults[(int)$result[0]]['fields'][] = array('key' => $result[1], 'value' => $result[2]);
		}
	}

What would be the best (fastest) way to go?

How did you write that demo?