Jordan Vit Jordan Vit - 5 months ago 40
PHP Question

Query two tables with Laravel and two WHERE conditions

I have table

orders
and table
payments
. I want to query table
orders
, joining table
payments
and to show with an order is paid and which isn't.

This is Order model

class Order extends Eloquent {
protected $table = 'orders';
protected $primaryKey = 'order_id';

public function paidorders() {
return $this->hasMany('payments', 'processed');
}
}


This is Payment model

class Payment extends Eloquent {
protected $table = 'payments';
protected $primaryKey = 'paymentID';

public function orders()
{
return $this->hasMany('Order', 'user_id');
}
}


And the User model

public function orders() {
return $this->hasMany('Order', 'user_id');
}


This is how I show currently orders only without status paid/not paid.

$orders = self::$user->orders()->get();
return View::make('site.users.orders', [
'orders' => $orders
]);


This is the query but I don't know how to implement it in Laravel

SELECT orders. * , payments. *
FROM orders
INNER JOIN payments ON orders.user_id = payments.userID
WHERE orders.user_id =2
AND payments.userID =2


self::$user->...
is the logged user. How will be used this in the
WHERE
clause?

I don't get it exactly how to build this query

UPDATE
dd($orders)


object(Illuminate\Database\Eloquent\Collection)#264 (1) { ["items":protected]=> array(1) { [0]=> object(Order)#260 (20) { ["table":protected]=> string(6) "orders" ["primaryKey":protected]=> string(8) "order_id" ["connection":protected]=> NULL ["perPage":protected]=> int(15) ["incrementing"]=> bool(true) ["timestamps"]=> bool(true) ["attributes":protected]=> array(1) { ["processed"]=> string(1) "1" } ["original":protected]=> array(1) { ["processed"]=> string(1) "1" } ["relations":protected]=> array(0) { } ["hidden":protected]=> array(0) { } ["visible":protected]=> array(0) { } ["appends":protected]=> array(0) { } ["fillable":protected]=> array(0) { } ["guarded":protected]=> array(1) { [0]=> string(1) "*" } ["dates":protected]=> array(0) { } ["touches":protected]=> array(0) { } ["observables":protected]=> array(0) { } ["with":protected]=> array(0) { } ["morphClass":protected]=> NULL ["exists"]=> bool(true) } } }

Answer
$query = Order::select(DB::Raw('payments.processed'))
    ->join('payments', 'orders.order_id', '=', 'payments.orderID')
    ->where('orders.user_id',  2)
    ->where('payments.userID', 2)
    ->get();
Comments