Fetch unique content on some attribute ?

Author Message

H-Works Agency

Wednesday 20 December 2006 11:39:45 am

How can i make a fetch content having unique result on given attributes :

For exemple :

Fetch all products but return only unique 'name' and 'price'.

This would add to the function something like this :

{fetch('content','list',hash('unique', array('name', 'price'))}

This would save a lot of filtering work.

EZP is Great

Claudia Kosny

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

H-Works Agency

Wednesday 27 December 2006 7:30:07 am

Hi Claudia :

In this case it would return the first encountered : You think its possible ?

I need this feature a lot or i am forced to do some tedious array manipulation inside templates code.

Thanx

EZP is Great

Claudia Kosny

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

H-Works Agency

Monday 01 January 2007 7:48:20 am

So there is no way to group by / unique / distinct on a child attribute value using a fetch function ?

As i really need this feature, which way should i try to get this working :

- Use extended_attribute_filter ?
- Creating a patch for the kernel fetch function ?
- Creating a custom fetch function ?

Thanx for the help.

Martin

EZP is Great

Claudia Kosny

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

H-Works Agency

Tuesday 02 January 2007 5:01:44 am

Thanx a lot for your answer...Things are a little clearer now.

I will keep you inform of the progress and will post the code here.

Due to the complexity of the sql queries i think i will go to this template operator solution.

In fact i tried to modify the createGroupBySQLStrings function in ezcontentobjecttreenode.php but its not so easy...even if it seems to me the best solution.

In fact this function only allow grouping by published/modified...why isn't it offering the ability to group by "attribute" like the sort_by parameter.

Happy New Year - Martin

EZP is Great

Claudia Kosny

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

Powered by eZ Publish™ CMS Open Source Web Content Management. Copyright © 1999-2014 eZ Systems AS (except where otherwise noted). All rights reserved.