PHP Versions Question posted in
You can find a list of all versions here.

Php versions – Typo3 – How to use the QueryInterface to order by the sum of children fields?

I’m trying to add an order by using the QueryInterface in Typo3, but I can’t figure out how to translate my mysql query to a Typo3 query.

Here’s my mysql query:

SELECT * FROM `primary_table` pt
ORDER BY (
 IFNULL(
  (select SUM(a.score) from child_table_1 a 
   left join child_table_1_mm b on a.uid = b.uid_foreign 
   where b.uid_local = pt.uid and a.uid = 3), 0) +
 IFNULL(
  (select SUM(a.score) from child_table_2 a 
   left join child_table_2_mm b on a.uid = b.uid_foreign 
   where b.uid_local = pt.uid and a.uid = 2), 0) +
 IFNULL(
  (select SUM(a.score) from child_table_3 a 
   where a.uid = pt.child_table_3_uid and a.uid = 0), 0)) 
desc;

So basically, I have a primary table and want to order the result of that table but according to the addition of the score from each related table. In my case, child_table_1 and child_table_2 have a relation 1:n with the primary table, while child_table_3 is 1:1.

I already have an order by using the QueryInterface.. something like

$query->setOrderings(array('pt.firstname'=>QueryInterface:ORDER_ASCENDING));

I tried to copy paste my mysql-order-by-version directly, and even tried with only one child to make things easier, even without the IFNULL, but I always have the error "The ColumnMap for property "SUM(a" of class "…" is missing.

I tried ordering it in php and not mysql directly, but there’s a limit of results, therefor my sort does not work properly since I don’t have every results.

I am able to write something like

$query->setOrderings(array('pt.child_table_1.score'=>QueryInterface:ORDER_DESCENDING));

but I can’t add a SUM in there, neither a condition for uid = 3 or the possibility to addition the score for each child.

The results have to be sorted when getting out of the database. I don’t know if something can be done using TCA configuration file.. I saw properties such as foreign_table when mapping a field, but I have no idea how I could make this work in my case.

How can I use this mysql order by logic with Typo3?

Tags:

2

Answers


  1. 0

    Usually in TYPO3 fields connected to related tables are supposed to hold the number of related children as a value. Each time you add or remove a child, the value of that field will be change. If it is 0, there are no child records connected yet.

    So instead of counting the number of related children by querying their tables you could just order by the values of those fields taken from the main table.

    Login or Signup to reply.
  2. 0

    I would add a special property in the model of the parent element. It should not be necessary to add it in the TCA definition.

    You can do the necessary calculations either in the model if this additional property is needed in general, either inside the specific controller action where this property is needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search