Cakephp3 query model

Question

Here is my Product Model:

$this->table('products');
$this->belongsTo('OrderProducts', [
    'foreignKey' => 'order_product_id',
    'propertyName' => 'order_product',
    'joinType' => 'INNER'
]);
$this->hasMany('RefundProducts', [
    'foreignKey' => 'product_id',
    'sort' => ['RefundProducts.created' => 'DESC'],
    'propertyName' => 'refund_products',
    'className' => 'RefundProducts'
]);

My query:

$result = $this->Products->find('all', [
    'contain' => [
        'RefundProducts' => [
            'PriceUnits',
            'conditions' => $refund_condition,
        ]
    ]
]);

but it get all product, i want to get only product have RefundProducts

score 1 · Accepted Answer · 2015-05-28 06:32:42Z

1

This is job for matching() method, which will create INNER JOIN with RefundProducts, so you will get only Products that have some RefundProducts. Conditions in contain limit only fetched associations

 $result = $this->Products->find()
    ->contain(['RefundProducts' => function ($q) use ($refund_condition) {
        return $q->where($refund_condition);
    }])
    ->matching('RefundProducts')
    ->distinct(['Products.id']);

I'm not sure what $refund_condition should do. This example will get Products that have some RefundProducts, but will contain RefundProducts only if $refund_condition is satisfied (so RefundProducts can get back as empty). Alternatively, based on what you want to filter, you can do it this way

->contain(['RefundProducts'])
->matching('RefundProducts', function ($q) use ($refund_condition) {
    return $q->where($refund_condition);
})

edited May 28, 2015 at 6:32

answered May 28, 2015 at 6:25

user1418909

Sign up to request clarification or add additional context in comments.

2 Comments

Cao Thế Cường Over a year ago

select product_type, refund_price_unit_id, sum(refund_price) from (select refund_price_unit_id refund_price_unit_id, (select product_type_id from products where id = a.product_id) product_type, refund_price from refund_products a) b group by product_type , refund_price_unit_id; i don't know how to query In Cakephp Model query

user1418909 Over a year ago

@Cao First, please read this guide: Query Builder. You can find there everything you need to create and further customize your queries. After that, if you will be still having problems to make your queries work, you can post another question with your specific problem.

Jatin Bansal · Accepted Answer · 2015-05-27 12:40:11Z

1

Cao Thế Cường, have you tried relational query like this:

$result = Products->find()->contain(['RefundProducts' => function ($q) {
   return $q
        ->select(['field1', 'field2'])
        ->where(['refunded_product' => true])]);

edited May 27, 2015 at 12:40

answered May 27, 2015 at 11:11

Jatin Bansal

87312 silver badges24 bronze badges

Comments

Bob · Accepted Answer · 2015-05-27 11:12:34Z

0

Use a where to check if a column of the related table (RefundProducts in this case) is used:

// in your query-object
$query->where(['Table.column IS NOT NULL']);

answered May 27, 2015 at 11:12

Bob

8481 gold badge8 silver badges22 bronze badges

Collectives™ on Stack Overflow

Cakephp3 query model

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related