Forums / General / attribute filtering - like not working.

attribute filtering - like not working.

« 
Previous topic
|
General
|
Next topic
 »
Author Message

Carlos Revillo

Thursday 31 August 2006 11:45:28 am

Hi. i'm trying to show some records of a list filtering them by his first letter.

so i've written something like:

{let grupos=fetch('content','tree',hash(
                                             'parent_node_id',2,
	        			     'sort_by',array('attribute',true(),229),
                                             'class_filter_type','include',
					     'class_filter_array',array('grupo'),
                                             'attribute_filter', array(array('grupo/grupo','like','A*'))
					))}

but it doesn't work. if i change 'like' operator for '>=' or another comparation, it works, but it doesn't when i use 'like'...

any ideas?

Claudia Kosny

Thursday 31 August 2006 1:07:26 pm

Hi Carlos

The attribute filter with 'like' should work fine, your code looks fine as well.
Therefore first make absolutely sure that there are nodes that fulfill the criteria in the filter.
Also check if you get any error messages in the debug output (especially something like 'attribute filter returned false' or so).

Enable the sql debug output and have a look at the generated query - do you see any LIKE statement in there? (If you have a lot of queries on your site, surround the fetch code with a {debug-timing-point id='foo'} ... {/debug-timing-point} and then look for 'foo' in the debug output.) Please post the resulting query here.

What version of EZ, PHP and MySQL do you use?

Claudia

Carlos Revillo

Thursday 31 August 2006 2:46:38 pm

hi. i enabled sql output and i didn't see any errors related with my query.

That's the query generated 

SELECT ezcontentobject.*,
                       ezcontentobject_tree.*,
                       ezcontentclass.name as class_name,
                       ezcontentclass.identifier as class_identifier
                       , ezcontentobject_name.name as name,  ezcontentobject_name.real_translation 
                FROM ezcontentobject_tree,
                     ezcontentobject,
                     ezcontentclass
                     , ezcontentobject_name 
                WHERE node_id IN ( 2 ) AND
                      ezcontentobject_tree.contentobject_id=ezcontentobject.id AND
                      ezcontentclass.version=0  AND
                      ezcontentclass.id = ezcontentobject.contentclass_id
                       AND 
 ezcontentobject.language_mask & 7 > 0 

                       and  ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
                                  ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and 
 ( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 4 ) >> 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) << 1 )
   <
     ( ezcontentobject_name.language_id & 1 )
   + ( ( ezcontentobject_name.language_id & 4 ) >> 1 )
   + ( ( ezcontentobject_name.language_id & 2 ) << 1 )
 )


SELECT ezcontentobject.*,
                       ezcontentobject_tree.*,
                       ezcontentclass.name as class_name,
                       ezcontentclass.identifier as class_identifier
                       
                       , ezcontentobject_name.name as name,  ezcontentobject_name.real_translation 
                   FROM
                      ezcontentobject_tree,
                      ezcontentobject,ezcontentclass
                      , ezcontentobject_name 
                      , ezcontentobject_attribute a0
                      , ezcontentobject_attribute a1 
                      
                   WHERE
                       path_string like '/1/2/%' and  
                      
                      
                                   a0.contentobject_id = ezcontentobject.id AND
                                   a0.contentclassattribute_id = 229 AND
                                   a0.version = ezcontentobject_name.content_version AND
 ( a0.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & a0.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a0.language_id ) ) & 4 ) >> 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a0.language_id ) ) & 2 ) << 1 )
   <
     ( a0.language_id & 1 )
   + ( ( a0.language_id & 4 ) >> 1 )
   + ( ( a0.language_id & 2 ) << 1 )
 ) 
 AND 
                      
                                       a1.contentobject_id = ezcontentobject.id AND
                                       a1.contentclassattribute_id = 229 AND
                                       a1.version = ezcontentobject_name.content_version AND 
 ( a1.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & a1.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a1.language_id ) ) & 4 ) >> 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a1.language_id ) ) & 2 ) << 1 )
   <
     ( a1.language_id & 1 )
   + ( ( a1.language_id & 4 ) >> 1 )
   + ( ( a1.language_id & 2 ) << 1 )
 ) 
 AND 
                            ( a1.sort_key_string = 'A'  ) AND 
                      ezcontentclass.version=0 AND
                      node_id != 2 AND
                      ezcontentobject_tree.contentobject_id = ezcontentobject.id  AND
                      ezcontentclass.id = ezcontentobject.contentclass_id AND
                      
                      
                       ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
                                   ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and 
 ( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 4 ) >> 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) << 1 )
   <
     ( ezcontentobject_name.language_id & 1 )
   + ( ( ezcontentobject_name.language_id & 4 ) >> 1 )
   + ( ( ezcontentobject_name.language_id & 2 ) << 1 )
 ) 

                      AND ezcontentobject_tree.is_invisible = 0
                       AND ((ezcontentobject.section_id in (1, 7))) 
                       AND 
 ezcontentobject.language_mask & 7 > 0

I'm working with ezpublish in a shared hosting.
MySQL - 4.1.21
PHP Version 4.4.4
Ez 3.8.3

Btw, php is running as cgi... i know is not the best option for ez but this is what i have...

Claudia Kosny

Saturday 02 September 2006 3:34:35 am

Hello Carlos

somehow the attribute filter does not work as it should. In your query there is no LIKE statement at all, I think the corresponding statement is ( a1.sort_key_string = 'A' ) which seems a bit strange to me.
Could you please tell me what type the class attribute grupo of the class grupo has?
Could you also try the like operator on another attribute of the class and check whether you get a LIKE statement in the corresponding query.

Greetings from Luxembourg

Claudia

Carlos Revillo

Saturday 02 September 2006 6:41:57 am

sorry, there was a mistake in my last post. here is the real query

SELECT ezcontentobject.*,
                       ezcontentobject_tree.*,
                       ezcontentclass.name as class_name,
                       ezcontentclass.identifier as class_identifier
                       
                       , ezcontentobject_name.name as name,  ezcontentobject_name.real_translation 
                   FROM
                      ezcontentobject_tree,
                      ezcontentobject,ezcontentclass
                      , ezcontentobject_name 
                      , ezcontentobject_attribute a0
                      , ezcontentobject_attribute a1 
                      
                   WHERE
                       path_string like '/1/2/%' and  
                      
                      
                                   a0.contentobject_id = ezcontentobject.id AND
                                   a0.contentclassattribute_id = 229 AND
                                   a0.version = ezcontentobject_name.content_version AND
 ( a0.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & a0.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a0.language_id ) ) & 4 ) >> 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a0.language_id ) ) & 2 ) << 1 )
   <
     ( a0.language_id & 1 )
   + ( ( a0.language_id & 4 ) >> 1 )
   + ( ( a0.language_id & 2 ) << 1 )
 ) 
 AND 
                      
                                       a1.contentobject_id = ezcontentobject.id AND
                                       a1.contentclassattribute_id = 229 AND
                                       a1.version = ezcontentobject_name.content_version AND 
 ( a1.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & a1.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a1.language_id ) ) & 4 ) >> 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & a1.language_id ) ) & 2 ) << 1 )
   <
     ( a1.language_id & 1 )
   + ( ( a1.language_id & 4 ) >> 1 )
   + ( ( a1.language_id & 2 ) << 1 )
 ) 
 AND 
                            ( a1.sort_key_string LIKE 'A%'  ) AND 
                      ezcontentclass.version=0 AND
                      node_id != 2 AND
                      ezcontentobject_tree.contentobject_id = ezcontentobject.id  AND
                      ezcontentclass.id = ezcontentobject.contentclass_id AND
                      
                      
                       ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
                                   ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and 
 ( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 4 ) >> 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) << 1 )
   <
     ( ezcontentobject_name.language_id & 1 )
   + ( ( ezcontentobject_name.language_id & 4 ) >> 1 )
   + ( ( ezcontentobject_name.language_id & 2 ) << 1 )
 ) 

                      AND ezcontentobject_tree.is_invisible = 0
                       AND ((ezcontentobject.section_id in (1, 7))) 
                       AND 
 ezcontentobject.language_mask & 7 > 0 

                 ORDER BY a0.sort_key_string ASC

btw, grupo is a text field...

Claudia Kosny

Saturday 02 September 2006 8:30:58 am

Hello Carlos

Now I am missing the class limitation in the query - unlesse you changed the fetch function you had in your initial posting. On the other hand this should not have any impact on the problem itself.
Be that as it may - the LIKE statement looks fine now and should work so I do not know at all why it does not work. I even used your query in my database (I just changed the number of the attribute and the number of the section to adapt it to my settings) and got nice results.

So to me this looks like you really do not have any objects that match the criteria A*. You can try whether you get any results using e.g. *a* as parameter, although at the end it does not matter as both should work fine.
The problem might also be caused by some strange MySQL settings (strange character set?), but this is pretty unlikely if all the other parts of EZ work fine.

Claudia

Carlos Revillo

Saturday 02 September 2006 9:06:02 am

Hi Claudia. Thanks so much for your help. I'll try to explain better what i'm doing, but sorry for my poor english.

I have a class called "grupo". one of its fields is also called "grupo". btw, i'll tell you group is a "table of musical bands" or something like that. so i wrote the query as you have seen.

I have made some changes to the query to see what's happening,

if i write 

let grupos=fetch('content','tree',hash(
				'parent_node_id',2,														'sort_by',array('attribute',true(),229),
				'attribute_filter',array(array('grupo/grupo','>=','A'))
				))}

i get many records, as expected. one of them is "Apse".

But if i write

{let grupos=fetch('content','tree',hash(
	'parent_node_id',2,
	'sort_by',array('attribute',true(),229),
	'attribute_filter',array(array('grupo/grupo','=','Apse'))
			))}

i get nothing... the same thing is happening with "like" operator...

btw, i have copied the queries and tried them in phpmyadmin. as you have said, like returns 0 records. But '>=' returns about 20 records.

So, if i get in this last query records like "Aroah" and "Apse", like operator would have to return at least these records i'm talking about, right?

Claudia Kosny

Saturday 02 September 2006 10:42:56 am

Hello Carlos

Now I have got an inkling of what might be going on.
If you check the query you can see that the comparison is done on the sort_key_string which is in lower case. Usually this should not matter as MySQL is not case sensitive but out of some reasons yours is. Thats why the >= operator works as the lowercase letter a is > than the uppercase letter A.
The only reason I can think of is that your database has a bin-collation instead of a ci collation.The most common collation for EZ 3.8.3 is utf8-general-ci or utf8-unicode-ci which are both case insensitive.

If that is truly the source of your problem you can either change your attribute filter to a lowercase a* or you can change the collation of your database. Please note that this might change your data irrevocably so if you intend to try it, make a copy of your database before.

Greetings from Luxembourg

Claudia

Carlos Revillo

Saturday 02 September 2006 11:12:21 am

Oh my god!. That was. All you're saying is right. i think for my purposes i'll only change the attribute filter to 'a*'. Thanks so much for your help. Greetings from Spain.

Carlos Revillo

Monday 11 September 2006 12:21:01 am

now i have a related question, i think.
i want to filter using '=' operator.
so i write

{set $productos=fetch('content','tree',hash(
				'parent_node_id',97,
				'sort_by',array('attribute',true(),215),
				'attribute_filter',array(215,'=','Aroah'),
				'class_filter_type','include',
				'class_filter_array',array('product')
))}

i enabled sql output and i don't find any query with the word "Aroah" in it...
Any ideas? thanks.

Claudia Kosny

Tuesday 12 September 2006 12:09:17 am

Hi Carlos

Check your debug output - most likely you have a message like 'attributefilter returned false' or so in there. The problem is that the attribute filter is an array whose first member is the word 'and' or 'or' (which defaults to 'and' if left out) and whose other parameters are arrays. So the correct structure of your attribute filter is

'attribute_filter',array(array(215,'=','Aroah'))

BTW: In the code you posted you can do without the sorting as you will have only nodes whose attribute 215 is set to Aroah. So sorting by attribute 215 is not necessary as all nodes have the same value there.

Greetings from Luxembourg

Claudia