MySQL Query - Condition not working

[rennecke]

Maybe someone can help me with some advise.
I have a cable list (entity 31) which holds a numeric field of cable length [264], a field project number [316], which is of type value from parent entity and a date field [266].
In another entity I like to sum all cable lenths of a specific project and an a certain date.
So I created a field MySQL Query with:
- The cable list (Entity 31) as "Select from entity".
- SUM([264]) as "Select field" and
- [316]=[319] AND DATE(e.date_added)=DATE([266]) as Condition

I don't get an error, it sums all cable lengths with no limitation by the condition.
The debug mode for this entity shows:

Code: Select all

select e.* ,IFNULL((select SUM((msq.field_264+0)) from app_entity_31 msq where msq.field_316=e.field_319 AND DATE(e.date_added)=DATE(msq.field_266) limit 1),0) as field_320 from app_entity_32 e where e.id>0 and e.parent_item_id in (select item_id from (select e.id as item_id from app_entity_25 e where e.id>0 ) as parent_entity_25 ) order by e.date_added desc limit 0, 30
[1.8100]

Even trying the individual condition [316]=[319] allown and DATE(e.date_added)=DATE([266]) allown, do not work.
As per the instruction under https://docs.rukovoditel.net/index.php? ... conditions this should work. SUM([264]) is shown for "Select field" and [316]=[319] is shown for "Condition".

I like to use this dynamically, so I switched this on.

What do I wrong? Some advise is highly appriciated.

[support]

You can not use function DATE because dates in app saved in timestamp. You have to use function FROM_UNIXTIME

Code: Select all

FROM_UNIXTIME(e.date_added,'%Y-%m-%d')=FROM_UNIXTIME(msq.field_266,'%Y-%m-%d')

For more examples you can find here https://docs.rukovoditel.net/index.php? ... difference
and here https://www.w3resource.com/mysql/date-a ... nction.php

[rennecke]

Thanks.
This works for the date part.
The comparison [316]=[319] was another issue. Both are "value from parent entity" fields.
I checked them with phpMyAdmin and found out that there is nothing in the field.
So, I guess the field in the database is a representation, for php to be transfered to html for viwewing in the web. All data will be generated, when the page is created. Ergo, there is nothing to compare.
As one of the entities [msq] is a grandchild of the relevant entity [30] to compare I did it with this:

Code: Select all

msq.parent_item_id IN (select n.id from app_entity_30 n where n.parent_item_id = e.parent_item_id)

Anyhow, it works