Query From Two Tables Using Laravel Query Builder + Mysql
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 | |
+--------------+------------------+------+-----+---------------------+-----------------------------+
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.
Related Questions
- → "failed to open stream" error when executing "migrate:make"
- → October CMS Plugin Routes.php not registering
- → OctoberCMS Migrate Table
- → OctoberCMS Rain User plugin not working or redirecting
- → October CMS Custom Mail Layout
- → October CMS - How to correctly route
- → October CMS - Conditionally Load a Different Page
- → Make a Laravel collection into angular array (octobercms)
- → In OctoberCMS how do you find the hint path?
- → How to register middlewares in OctoberCMS plugin?
- → Validating fileupload(image Dimensions) in Backend Octobercms
- → OctoberCMS Fileupload completely destroys my backend
- → How do I call the value from another backed page form and use it on a component in OctoberCms