I can't get this simple query working [SOLVED]

Need Help
1

Hey guys.
It seems everything is set up fine in my Tables scripts, but I definitely can’t get through this.
I don’t understand why I can write this simple query in 5s in MySQL:

SELECT rides.id AS ride_id, workshops.translation_key, workshops.slug, rides_workshops.level FROM rides JOIN rides_workshops ON rides.id = rides_workshops.ride_id JOIN workshops ON rides_workshops.workshop_id = workshops.id

But how on Earth does it take me 1 hour to write with Cake?
Don’t get me wrong, I love Cake, but sometimes I think it’s just simpler in plain old sql.
Anyway, I would like to translate this query into “Cake code”.

Right now, I have RidesTable with this in my initialize function:

$this->belongsToMany('Workshops', [ 'foreignKey' => 'ride_id', 'targetForeignKey' => 'workshop_id', 'through' => 'RidesWorkshops' ]);

I have RidesWorkshopsTable with this:

$this->belongsTo('Rides');
$this->belongsTo('Workshops');

And finally, WorkshopsTable with this.

$this->belongsToMany('Rides', [ 'foreignKey' => 'workshop_id', 'targetForeignKey' => 'ride_id', 'through' => 'RidesWorkshops' ]);

At first sight, I thought this was a simple matter of contain:

$result = TableRegistry('Rides')->find()->contain(['Workshops', 'RidesWorkshops'])->all();

But “it” says there is no association between Rides and Workshops.
And I get it, since their association is hidden behind the RidesWorkshops table.
Probably I didn’t really understand what was all of this about.
What’s wrong ? :frowning:
Thanks for helping me!

2

what you need is find()->matching(‘Workshops’)

1 Like
3

Hello,
Sounds way better ! :slight_smile: I knew there was a simpler way!
But, when I run my SQL query, I have 42 results.
With TableRegistry::get('Rides')->find()->matching('Workshops')->all(), I have 46 results… What’s different?

EDIT: after counting all the lines in phpMyAdmin, I really have 46 results in my SQL query as well.
My phpMyAdmin seems to have a bug.
Thanks for your help! :smiley:

1 Like