Ad

Query From Two Tables Using Laravel Query Builder + Mysql

- 1 answer

I have two tables, one called quotations and the other called invoices. I want to retrieve all quotations without invoices. Below is the code I have so far. I can only retrieve all quotations. How can I modify this query

$quotations = Quotation::lists('id', 'id');


mysql> describe quotations;
+---      ----  --------+------------------+------+-----+---------------------+-----------------------------+
| Field         | Type             | Null | Key | Default             | Extra                       |
+---------------+------------------+------+-----+---------------------+-----------------------------+
| id            | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| customer_id   | int(10) unsigned | NO   | MUL | NULL                |                             |
| employee_id   | int(10) unsigned | NO   | MUL | NULL                |                             |
| exchange_rate | int(11)          | NO   |     | 0                   |                             |
| remark        | varchar(255)     | NO   |     |                     |                             |
| created_at    | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at    | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
+---------------+------------------+------+-----+---------------------+-----------------------------+
mysql> describe invoices;
+--------------+------------------+------+-----+---------------------+-----------------------------+
| Field        | Type             | Null | Key | Default             | Extra                       |
+--------------+------------------+------+-----+---------------------+-----------------------------+
| id           | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| quotation_id | int(10) unsigned | NO   | MUL | NULL                |                             |
| employee_id  | int(10) unsigned | NO   | MUL | NULL                |                             |
| amount       | int(11)          | NO   |     | 0                   |                             |
| balance      | int(11)          | NO   |     | 0                   |                             |
| created_at   | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at   | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
+--------------+------------------+------+-----+---------------------+-----------------------------+
Ad

Answer

You can use the following:

Quotation::has('invoices', '<', 1)->get();

This above code assumes you have a relationship set up in your Quotation model, ie:

class Quotation 
{
    public function invoices()
    {
        return $this->hasMany('\Models\Invoice');
    }
}

The has method will check for the total number of items in the relationship you have defined as the first argument invoices. The second argument is the comparison of less than, the third is the count you want to compare to. So this will search for all Quotations with an invoice count of less than one.

You can read more about querying relations here under Querying Relationship Existence.

Ad
source: stackoverflow.com
Ad