order by categories

[wilku]

Hello. This question is somewhat similar to blackhorse's thread from today, but I didn't want to break into his thread.

I have 2 tables: products and products_categories_xref
As you can see, the second one stores crossreference between products and their categories. There are 3 levels of categories. Categories ids are uniqe integers (independent of their level).
What I want to do is to search for products with some conditions that belong to either main category c1, subcategory c2 or second-level subcategory c3 and sort the list in this order:
- order of relevance - first products found for category c3 then c2 and last c1
- specified order inside category

Thus far I have come up with this:
The easiest way would be to

SELECT DISTINCT products.id, list_of_other_fields
FROM products, products_categories_xref
WHERE products.id = products_categories_xref.category_id
 AND (products_categories_xref.category_id = 'c3'
         OR products_categories_xref.category_id = 'c2'
         OR products_categories_xref.category_id = 'c1')
 AND whatever conditions here
ORDER BY products_categories_xref.category_id, whatever order inside category
LIMIT start, offset

But then categories ids aren't sorted in this way that c1, c2, c3 are in some order - c2 can be greater then c1 but c3 can be the lowest.
Another solution:

(SELECT DISTINCT products.id, list_of_other_fields
FROM products, products_categories_xref
WHERE products.id = products_categories_xref.category_id
 AND products_categories_xref.category_id = 'c3'
 AND whatever conditions here
ORDER BY whatever order inside category)
UNION
(SELECT DISTINCT products.id, list_of_other_fields
FROM products, products_categories_xref
WHERE products.id = products_categories_xref.category_id
 AND products_categories_xref.category_id = 'c2'
 AND whatever conditions here
ORDER BY whatever order inside category)
UNION
(SELECT DISTINCT products.id, list_of_other_fields
FROM products, products_categories_xref
WHERE products.id = products_categories_xref.category_id
 AND products_categories_xref.category_id = 'c1'
 AND whatever conditions here
ORDER BY whatever order inside category)
LIMIT start, offset

This has disadvantage as well - product can belong to category c1 as well as c2 as well as c3 (in most cases it will). So no advantage of distinct record (that's what I do right now - use this query and eliminate doubles inside php). But start and offset are useless here.

Anyone has better ideas on how to achieve this? can you say to mysql "order by c3 first c2 second c1 third, regardless of c1, c2, c3 values"?

[sneakyimp]

First off, that first bit of SQL you posted connects a product's id to a category id:

wilku wrote:

WHERE products.id = products_categories_xref.category_id

That doesn't sound right to me at all.

There are many ways to skin this cat. It's not clear exactly what you want so perhaps you should start phrasing your questions more exactly as a programmer rather than as a lay person.

You decisions to make:
If a product belongs to multiple categories, do you want to list it multiple times or not?
Do you want to list the category for each product? If not, just do a SELECT distinct and order the products alphabetically or by id or something.
* If you do want to list the category for each product, you either have to list multi-cat products for each category or you have to write your code to aggregate all the categories for a particular product, listing each product once with possibly multiple categories. This would be done by looping through your product results.

And so on....

[wilku]

Of course it should be

WHERE products.id = products_categories_xref.product_id

As for other things:
I want distinct records of products, no matter how many categories they are in. It is just sorting by category that I want to achieve. If a match is found within level 3 category - it suits best. If the same product occurs with c2 category - throw this occurence out. The same for c1. That's what I mean by "order by relevance". Most detailed category should be displayed first.
So the first sql I gave is the closest to what I want to achieve. It is just that if c1=150 c2=167 c3=143 for example, I would like to order it category_id 143,167,150, whereas my sql will give me products from category 143 first 150 second 167 third.

[tomhath]

You don't want DISTINCT. I think what you want is to GROUP BY the products.id and other fields except category_id, then select the MIN(category_id).