Date Field as filter, date are grouped per month, how to avoid grouping

ggppdk
Offline
Administrator

9 years 3 months ago #60826 by ggppdk

Replied by ggppdk on topic Date Field as filter, date are grouped per month, how to avoid grouping

Chabi01 wrote: Ho !
If i try "date selector, i get this !

Code:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%) ORDER BY i.created DESC, i.title ASC LIMIT 0, 10' at line 1 SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT i.id FROM #__flexicontent_items_tmp AS i JOIN #__flexicontent_types AS ty ON i.type_id = ty.id JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id JOIN #__categories AS c ON c.id = i.catid WHERE 1 AND ( i.state IN (1, -5) ) AND ( ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= UTC_TIMESTAMP() ) ) AND ( ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= UTC_TIMESTAMP() ) ) AND ( i.language LIKE 'fr%' OR i.language="*" ) AND ty.access IN (0,1,1,5) AND c.access IN (0,1,1,5) AND i.access IN (0,1,1,5) AND i.id IN (SELECT DISTINCT rel.item_id FROM #__flexicontent_fields_item_relations as rel WHERE rel.field_id=15 AND DATE_FORMAT(rel.value, "%Y-%m-%d") LIKE DATE_FORMAT('2016-03-23', "%Y-%m-%d") %) ORDER BY i.created DESC, i.title ASC LIMIT 0, 10

Hello

the custom format comes FROM language string
if language string is bad then SQL query will break

-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...

Please Log in or Create an account to join the conversation.

Chabi01
Topic Author
Offline
Platinum Member

9 years 3 months ago - 9 years 3 months ago #60827 by Chabi01

Replied by Chabi01 on topic Date Field as filter, date are grouped per month, how to avoid grouping

Hi again,
I didn't understand this Georges...

My 2 last questions are linked i think (the sql broken and the langage string) but how can i get rid of this ?
Note that if i use the simple dropbown list, i have not this issue.
Where can i check this langage strings ?
EDIT : you mean that if i remove the string in my format all will be fine ?

Last edit: 9 years 3 months ago by Chabi01.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 3 months ago #60831 by ggppdk

Replied by ggppdk on topic Date Field as filter, date are grouped per month, how to avoid grouping

Hello

about correct language it can be set with:
SET lc_time_names = 'fr_FR';

- we will retrieve current language from Joomla and execute the above
i though this was already done by Joomla ?

i will check and add it
i have opened this:
github.com/FLEXIcontent/flexicontent-cck/issues/497

will fix it,

about SQL error i will test our built-in language strings in english language file are good
- but anyway even if they are not in your case then you can just type a custom one

Please Log in or Create an account to join the conversation.

Chabi01
Topic Author
Offline
Platinum Member

9 years 3 months ago #60843 by Chabi01

Replied by Chabi01 on topic Date Field as filter, date are grouped per month, how to avoid grouping

Thanks again for your time Georges.
Where can i set the langage as you wrote with "Set lc_time_names" ?
In the module code ? in another place ?
To try to resolve this, i change the translation in GB_gb.ini of the language for Joomla but it didn't works (the format are retrieve in another file obviously).
Sorry to be so a "pain in the a..." Georges...

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 3 months ago #60853 by ggppdk

Replied by ggppdk on topic Date Field as filter, date are grouped per month, how to avoid grouping

Hello

no, this is something we should do (fix)

- please wait for fix in next public package

i am not sure why this is not set already correctly, but will make sure it is called

The following user(s) said Thank You: Chabi01

Please Log in or Create an account to join the conversation.

Chabi01
Topic Author
Offline
Platinum Member

9 years 3 months ago #60861 by Chabi01

Replied by Chabi01 on topic Date Field as filter, date are grouped per month, how to avoid grouping

An little thing more : when you use a date field, it could be great if you can tell "show only the date after this date : today, this week, this month, between these date, etc..). It is possible in the other module but not this one (or i didn't saw it...)

Thanks for all your time Georges

Xavier

Please Log in or Create an account to join the conversation.