Cakephp 3.5 InnerJoin

join query in cakephp
join cake php 3
cakephp 1.3 join
cakephp order by
cakephp or statement
distinct in cakephp
cakephp database
cakephp behavior

I have following situation. i have a table "drivers_events" in this there are stored the drivers which are booked for an event. Now I would like to do following

SELECT
    Driver.id,
    Driver.name 
FROM
    drivers AS Driver 
    INNER JOIN
        drivers_events AS Listing 
        ON Driver.id = Listing.driver_id 
WHERE
    Listing.event_id = 83 
ORDER BY
    Driver.name ASC;

If I run it as a SQL query in phpmyadmin, it works perfectly and I get the correct id and name from the table "drivers".

When I do this in my controller like this

$drivers = $this->Jobs->query("
    SELECT
        Driver.id,
        Driver.name 
    FROM
        drivers AS Driver 
        INNER JOIN
            drivers_events AS Event 
            ON Driver.id = Event.driver_id 
    WHERE
        Event.event_id = $activeevent 
    ORDER BY
        Driver.name ASC
");

then i get the complete array of jobs which created in the past.

What did i do wrong ?

It would be great if someone could help me.

Thanks in advance

I tried a lot more. I have come to the conclusion that I can read out the drivers who are assigned to an event.

in the controller it looks like this

 $drivers = $this->Jobs->drivers_events->find('list',
      [
      'conditions' =>
      [
           'drivers_events.event_id' => $this->request->session()->read('Event.active')
      ],
      'keyField' => 'driver_id',
      'valueField' => 'driver_id'
      ]
 );

In the model

 $this->belongsTo('drivers_events', [
      'foreignKey' => 'event_id',
 ]);

So now I get the right drivers for a driving job within an event. But how do I get the name of the driver from the "Drivers" table instead the ID ?

Thx for help

Class Cake\ORM\Query, innerJoinWith() public. Creates an INNER JOIN with the passed association table while preserving the foreign key matching and the custom conditions that were  Cakephp version: 3.5. I have a claims table. A project has many claims. A claim has one project, a number (1, 2, 3 and running), and each number may be revised multiple times. The combination of project_id, number and rev_number makes a uniqe claim. Table looks something like this (on the right hand I have marked the rows I want in a query):

Event key word is reserved for SQL. Please do not use it for table alias. Or use such this method.

drivers_events AS `Event`

Hope to be useful.

$result = $this->Jobs->find()
        ->enableAutoFields(true)
        ->enableHydration(false)
        ->select([
            'id' => 'Driver.id',
            'Title' => "Driver.name"
        ])
         ->join([
            'Events' => [
                'table' => 'drivers_events',
                'type' => 'INNER',
                'conditions' => [
                    'Jobs.event_id = Events.id',

                ],
            ]
        ])
        ->join([
            'Driver' => [
                'table' => 'drivers',
                'type' => 'INNER',
                'conditions' => [
                    'Events.driver_id = Driver.id',
                ],
            ]
        ])
        ->where([
            'Events.id' => $activeevent
        ])
        ->group('Driver.id')
        ->toArray();

Ref: Cakephp 3 Adding Joins

Associations - Linking Tables Together - 3.8, joinType: The type of the join used in the SQL query. Accepted values are '​LEFT' and 'INNER'. You can use 'INNER' to get results only where the association​  C CakePHP 3.5 Red Velvet API. Overview; Tree; Deprecated; Version: 3.5 . 3.8; 3.7; 3.6; 3.5; 3.4; 3.3; 3.2; 3.1; 3.0; 2.10; 2.9; 2.8

Select Drivers.id, drivers.accountname

FROM TABLE__

Join table__
Join table__

And (Brand) Totaljobs.com

Having Count <4

group By Driver.id, Drivers.Accountname

Order by (Drivers.accountname)

Class Cake\Database\Query, innerJoin( string|array $table , string|array| Cake\Database\ExpressionInterface $​conditions = [] , array $types = [] ). Adds a single INNER JOIN clause to the  FROM articles Articles INNER JOIN tags Tags ON Tags.name = 'cake' INNER JOIN articles_tags ArticlesTags ON ArticlesTags.tag_id = Tags.id AND ArticlesTags.articles_id = Articles.id This function works the same as matching() with the difference that it will select no fields from the association.

How to use Inner Join..? - Need Help, There is quite a lot of information available in the book. https://book.cakephp.org/​3.0/en/orm/associations.html and https://book.cakephp.org/3.0/en  CakePHP 3.5.0 Released. The CakePHP core team is happy to announce the immediate availability of CakePHP 3.5.0. This is the first stable release of 3.5.0. 3.5.0 provides a number improvements both large and small to CakePHP. It also deprecates several features that will be removed in 4.0.0.

Retrieving Data & Results Sets - 3.8, Prior to 3.5.0 you would use contain(['Comments' => function () { }]) $query You can create just the INNER JOIN that matching() uses with innerJoinWith() :. Como faço um inner join para relacionar uma tabela de artigos com uma tabela de artigos favoritos usando o cakephp 3. tabela de favoritos: CREATE TABLE IF NOT EXISTS `favorites` ( `id` int(11) NO

wrong INNER JOIN on an self-referencing belongsToMany , I started with cakephp 3 some days ago and haven't dug so deep in the core to figure out where i should start searching. Whether it is in the "Cake  The Query Object¶. The easiest way to create a Query object is to use find() from a Table object. This method will return an incomplete query ready to be modified. You can also use a table’s connection object to access the lower level Query builder that does not include ORM features, if necessary.

Comments
  • Maybe a tad offtopic, but why are you using a raw query instead of the query builder? Also note that what you are showing there is a possible SQL injection vulnerability, never ever insert bindable values into queries directly, especially not if it's user data!
  • Thanks for the information. I'd love to use the Query Builder, but I'm honest and do not look that way. Since I'm quite fit in SQL, I wanted to solve it just this way. But maybe you can give me a tip as I can implement the whole CakeLike so well in QueryBuilder. Especially since it does not come out at cake what I wanted to achieve.
  • Hi omid, thanks for your answer but this doesn't solves my problem. I will get always the complete drivers table when I do it in the controller. when i do it as sql query in phpmyadmin, it works great.