0

I'm working on an application for iceskaters with cakephp. A part of my database shema is like this :

database shema

Nations-(1,n)-Skaters-(1,n)-Participations-(n,1)-Competitions

Participations has a rank field and Competitions has a date field

I want to have an hasOne association from Skaters to the last Participation (of this skater) according to the date field in the table Competitions. I need that in model because i want to order Skaters by the last Participation or by the rank obtained during the last participation.

I don't know if it is possible with cakephp v3.x

In MySQL i write a query that seems to works :

Select s.id, c.title, c.date, p.id, p.rank 
from
(
    SELECT skater_id, max(competitions.date) as maxdate 
    FROM `participations`
    inner join competitions on competitions.id = participations.competition_id
    group by skater_id
) as x
inner join participations as p on p.skater_id = x.skater_id
inner join competitions as c on c.id = p.competition_id
inner join skaters as s on x.skater_id = s.id and maxdate = c.date

I've found a very usefull post but i'm not able to adapt it to my case (filtering field in third table not second one). The only result was : An Internal Server Error Occurred. So too dificult for me to debug without information.

Regards

ndm
  • 59,784
  • 9
  • 71
  • 110
SchönmannT
  • 1
  • Make sure that you [**enable debug mode**](https://book.cakephp.org/3.0/en/development/configuration.html#general-configuration) in `config/app.php` for more error details, also check the logs (`logs/*.log`). That being said, it's hard to give any overly concrete advice without knowing how exactly you are querying things via the ORM. As can be seen in the linked question, it's possible to create such associations that you are describing, but you can't put them in the `from` clause, you'd have to reconsider how to query things. Maybe show what you've tried so far that triggered the error? – ndm Jan 31 '19 at 22:00
  • Also think about whether you really need an association for this... Would you need it often? Would you use the association in various types of different queries? Maybe a custom finder would do it too. – ndm Feb 01 '19 at 01:21

0 Answers0