Searching records conditions with hasMany model Cakephp3

Question

I have Orders model with hasMany OrderProducts model. below is my code.I have added search functionality using DataTable jquery plugin.Each OrderProducts contain ref_code.

I want to add search like fetch all Orders which has OrderProducts which contains ref_code GWG-SC-001(B) or whatever user enter in search field.

OrderTable.php

namespace App\Model\Table;

use Cake\ORM\Table;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\Validation\Validator;
use Cake\ORM\TableRegistry;
use Cake\I18n\Time;

class OrdersTable extends Table
{
    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public $actsAs = array('Containable');

    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->table('orders');
        $this->displayField('id');
        $this->primaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsTo('Users', [
            'propertyName' => 'Users',
            'foreignKey' => 'user_id',
            'className' => 'Users'
        ]);

        $this->belongsTo('OrderStatus', [
            'propertyName' => 'OrderStatus',
            'foreignKey' => 'order_status_id',
            'className' => 'OrderStatus'
        ]);

        $this->hasMany('OrderProducts', [
            'className' => 'OrderProducts',
            'foreignKey' => 'order_id',
            'className' => 'OrderProducts'
        ]);


        $this->hasMany('OrderPayments', [
            'propertyName' => 'OrderPayments',
            'foreignKey' => 'order_id',
            'className' => 'OrderPayments'
        ]);


    }
}


/**
 * [getOrdersList description]
 * @param  [type] $data [description]
 * @return [type]       [description]
 */
public function getOrdersList($data, $user_id = null)
{
    $orderelem = $data['columns'][$data['order'][0]['column']]['name'];
    $ordertype = $data['order'][0]['dir'];
    $offset = $data['start'];
    $limit = $data['length'];
    $conditions = array("Orders.payment_status <>" => 0);

    if (!empty($data['search']['value'])) {
        $like = '%' . trim($data['search']['value']) . '%';
        $like2 = $data['search']['value'];
        $orValues = array("Order Received");

        if (strpos($data['search']['value'], 'Status:') !== false) {
            $data['search']['value'] = str_replace('Status: ', '', $data['search']['value']);
            $orValues = explode("|", $data['search']['value']);
        }

        $conditions[] = array(
            "Orders.payment_status <>" => 0,
            'OR' => [
                'Users.first_name LIKE' => $like,
                'Users.last_name LIKE' => $like,
                'Users.title LIKE' => $like,
                'Orders.order_number LIKE' => $like2,
                //'Orders.quantity_count LIKE'    => $like2,
                //'Orders.all_total LIKE'         => $like2,
                'Orders.tracking_number LIKE' => $like,
                'OrderStatus.title' => $like,
                'OrderProducts.ref_code' => $like, // this is not working
            ]
        );
        if (!empty($orValues)) {
            $conditions[0]['OR']['OrderStatus.title IN'] = $orValues;
        }
    }

    $data2 = $this->find('all')
        ->select([
            'id', 'order_number', 'order_type', 'created', 'tracking_number',
            'delivery_methods_id', 'order_status_id', 'quantity_count',
            'all_total', 'payment_status', 'is_allow_final_payment'
        ])
        ->contain([
            'Users' => function ($u) {
                return $u->select(['title', 'first_name', 'last_name']);

            },
            'OrderStatus' => function ($os) {
                return $os->select(['status' => 'OrderStatus.title']);
            },
            'OrderProducts'
        ]);

    if ($user_id) {
        $data2 = $data2->where(['user_id' => $user_id])->andWhere($conditions);
    } else {
        $data2 = $data2->where($conditions);
    }

    $data2 = $data2->limit($limit)
        ->order([$orderelem => $ordertype])
        ->offset($offset)
        ->toArray();

    return $data2;
}

When I try this its giving below error:

"message": "[PDOException] SQLSTATE[42S22]: Column not found: 1054 Unknown column OrderProducts.ref_code in where clause

Please help me in this issue.

below query is generating:

SELECT
    Orders.id AS `Orders__id`,
    Orders.order_number AS `Orders__order_number`,
    Orders.order_type AS `Orders__order_type`,
    Orders.created AS `Orders__created`,
    Orders.tracking_number AS `Orders__tracking_number`,
    Orders.delivery_methods_id AS `Orders__delivery_methods_id`,
    Orders.order_status_id AS `Orders__order_status_id`,
    Orders.quantity_count AS `Orders__quantity_count`,
    Orders.all_total AS `Orders__all_total`,
    Orders.payment_status AS `Orders__payment_status`,
    Orders.is_allow_final_payment AS `Orders__is_allow_final_payment`,
    Users.title AS `Users__title`,
    Users.first_name AS `Users__first_name`,
    Users.last_name AS `Users__last_name`,
    OrderStatus.title AS `status` 
FROM
    orders Orders 
    LEFT JOIN
        users Users 
        ON Users.id = 
        (
            Orders.user_id
        )
    LEFT JOIN
        order_status OrderStatus 
        ON OrderStatus.id = 
        (
            Orders.order_status_id
        )
WHERE
    (
        Orders.payment_status \u003C\u003E :c0 
        AND 
        (
            Orders.payment_status \u003C\u003E :c1 
            AND 
            (
                Users.first_name like :c2 
                OR Users.last_name like :c3 
                OR Users.title like :c4 
                OR Orders.order_number like :c5 
                OR Orders.tracking_number like :c6 
                OR OrderStatus.title like :c7 
                OR OrderProducts.ref_code like :c8 
                OR OrderStatus.title in 
                (
                    :c9
                )
            )
        )
    )
ORDER BY
    Orders.created desc LIMIT 25 OFFSET 25

don't you think you should put `like`. `OrderStatus.title LIKE ' => $like, 'OrderProducts.ref_code LIKE ' => $like,` — urfusion, Feb 24 '18 at 10:56
check the log for query and then run that on phpmyadmin directly and paste that in your question too. One more thing do you have `ref_code` field in your `OrderProducts` table? — urfusion, Feb 24 '18 at 11:00
I have updated generated query above, i dont know why Left join not created with OrderProducts — Dhara Parmar, Feb 24 '18 at 11:02
does your query work after removing `OrderProducts.ref_code` from condition? — urfusion, Feb 24 '18 at 11:16
yes irs working perfectly after I remove OrderProducts.ref_code — Dhara Parmar, Feb 24 '18 at 11:17
Can you add `'joinType' => 'INNER'` in `$this->hasMany('OrderProducts', [ 'className' => 'OrderProducts', 'foreignKey' => 'order_id', 'className' => 'OrderProducts', 'joinType' => 'INNER ]);` — urfusion, Feb 24 '18 at 11:25
@urfusion Contained `hasMany` associations will _always_ be retrieved in a separate query! — ndm, Feb 24 '18 at 12:27

score 2 · Accepted Answer · answered Feb 25 '18 at 02:11

When you use 'contain', it often actually generates separate queries for those contained models. As you can see, your "generated query" doesn't actually include OrderProducts, so in this case, it's generating a separate query for OrderProducts, then combining the results after. Because of this, you cannot condition against a field that't not included in the query.

TLDR: Instead of containing OrderProducts, if you want to add conditions against another model, use a join instead.

Depending on your setup, you may be able to specify the joinType to INNER for the association.

If not, you can always just inner join as specified in the book here: https://book.cakephp.org/3.0/en/orm/query-builder.html#using-innerjoinwith

Searching records conditions with hasMany model Cakephp3

1 Answers1