Fetch unique content on some attribute ?

Thursday 21 December 2006 11:15:39 am

Hi Martin

Just for clarification:
If you have two contentobjects, say
id: 1
name: a
price: 55
colour: red
id: 2
name: a
price: 55
colour: black

Should your fetch return the first, the second, an arbitrary one or none (as they are not unique)?

Claudia

Thursday 28 December 2006 4:25:27 pm

Hi Martin

Unfortunately on second thought I don't think this is possible (or at least not easily done) . My first thought was to use a subquery to fetch the max or min node id grouped by your unique criteria. This way you would have gotten just one entry for each combination. Then you could have fetched the nodes with these node ids. This approach works fine if you use node related data like priority or name in your unique criteria. But if you use attributes of the underlying object like price grouping is not possible (I think). And without grouping I don't not know how to fetch data that is unique on some attributes.

So sorry, but it looks like I cannot help you. You might be able to speed filtering up a bit if you do the filtering in PHP but I am not sure whether this is worth the work.

Claudia

Monday 01 January 2007 3:34:43 pm

Hi Martin

I am not that proficient with SQL so I might be wrong here, but I don't see any even halfways acceptable way to use something like distinct or group by for filtering on multiple attributes. The problem is that each attribute has it's own row. It might thus be possible to first group by the name and fetch the ids of objects that are unique by their name. Then you could use the result of this and group by the price and so on. Unfortunately apart from being terrible inefficient there is no way to fetch content that is unique by the combination of name and price like this. So I think an extended attribute filter or something else SQL related not a solution to this problem (although I certainly wouldn't mind being proven wrong here).

Patching the fetch function is not that easy. eZContentObjectTreeNode::subtree creates the query string out out of the parameters and then calls ezdb::arrayquery with this string. As changing the SQL does not seem to be feasible, you could try to patch ezdb::arrayquery according to your needs (e.g. checking whether there is already a row with certain attributes in the resulting array). But as this function is used by about all other queries, this is quite risky business. After the call to ezdb::arrayquery the function eZContentObjectTreeNode::makeObjectsArray is called to create objects out of each row. You could maybe add you filter here but again this function is used about everywhere so changing this is rather risky.

You could try to create your own fetch function which is basically a copy of the eZContentObjectTreeNode::subtree function and the functions used in there and add your check for uniqueness in your copy of ezdb::arrayquery so you have less rows in the returned resultset. I don't know whether this is a good solution, at least you will have to check at each EZ update whether this will still work.

An easier option would be just to write a template operator that takes a hash of the names of the unique attributes and the resultset of the fetch function and then iterates through the resultset and returns an array of unique content. This is certainly the easiest, most maintenable and flexible solution, although it will of course not be as fast.

Well, I am curious to see what solution you will finally end up with, so I would appreciate if you could post at least an outline of your solution. Thanks

Claudia

Tuesday 02 January 2007 11:37:30 am

Hi Martin

The problem comes when you want to group by several attributes. This works fine as long as the attributes are in the same row in the underlying SQL table. So you could group by published and owner_id at the same time (as they are in the same row in the ezcontentobject table), but grouping by data_map attributes like e.g price and name at the same time is not possible (as each of the attributes has its own row).

So basically grouping by multiple attributes that are in the data_map of an object is not feasible.

Theoretically you could create a temporary table which holds all the attribute for one object in a row, but this puts quite a strain on your mysql server and is not easy to implement.

So I still think that a simple template operator that takes care of filtering duplicates is the best solution. It is very flexible, takes just a few minutes to write and will work on all versions of EZ.

Good luck

Claudia