CODEWITHSUNDEEP
phpmysqlcakephpcakephp-3.0
aniga
aniga

Reputation: 117

cakephp 3 using contain in a sub associated table

I have 4 tables, Proposals, Bids, Users and Drivers. Proposals has Many bids and Bids has many Users and finally Drivers belong to Users.

What I'm trying to do is get all bids for proposal 1 which should show Proposal Name, Bid number, User details who bid including the fields in driver table which hold some specific details of the user.

My schema is as below:

    CREATE TABLE `proposals` (
      `id` int(11) NOT NULL,
      `email` varchar(200) NOT NULL,
      `item` int(11) NOT NULL,
      `desc` varchar(100) NOT NULL,
      `sess` varchar(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE `bids` (
      `id` int(11) NOT NULL,
      `proposal_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `amount` double NOT NULL,
      `session_code` varchar(255) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    CREATE TABLE `users` (
      `id` int(11) NOT NULL,
      `First_Name` varchar(200) DEFAULT NULL,
      `Last_Name` varchar(200) DEFAULT NULL,
      `role` varchar(200) NOT NULL,
      `Mobile_No` int(11) DEFAULT NULL,
      `Membership_Start` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `Address` varchar(255) DEFAULT NULL,
      `Post_Code` varchar(255) DEFAULT NULL,
      `email` varchar(50) DEFAULT NULL,  
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE `drivers` (
      `id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `verified` varchar(255) NOT NULL,
      `dealno` varchar(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here is the code I'm using which is failing - I know I have got it wrong:

     $query = $this->Bids->find()    
        ->contain([
            "Users",
            "Drivers",
            "Proposals"

        ])
        ->matching("Proposals", function($q){
            return $q->where(["proposals.id" => $id ]);
        });

        $bid = $this->paginate($query);

Any help would be highly appreciated.

Upvotes: 2

Views: 1847

Answers (2)

arilia
arilia

Reputation: 9398

you can also use dot notation to traverse the relationships

$proposal = $this->Proposals->get($id, [
    'contain' => ['Bids.Users.Drivers']
]);

https://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#eager-loading-associations-via-contain

Upvotes: 2

aniga
aniga

Reputation: 117

I fixed this with the following:

 $proposal = $this->Proposals->get($id, [
        'contain' => ['Bids' => ['Users' => ['Drivers']]]

    ]);

Upvotes: 3

Related Questions