Question: I have 2 tables (example) which have something like this... In the Invoice table, I have 5 records and in the Quotation table, I have 3.

| Invoice |
| --- |
| id |
| price |
| inv\_desc |
| invoice\_date |
| balance |

| Quotation |
| --- |
| id |
| qty |
| quo\_desc |
| quo\_date |

I want to get all records (\*) from both tables between date 1/1/2021 and 28/2/2021, and save it in a single collection, in order to loop and display in list. Since the number of columns and name of the date column of the 2 tables are different, how do I retrieve the records sort by ascending dates? The expected output (example) should be:

| No. | Description | Quantity | Price | Balance | Date |
| --- | --- | --- | --- | --- | --- |
| 1. | Quotation | 5 |  |  | 1/1/2021 |
| 2. | Invoice |  | 154.90 | 154.90 | 12/1/2021 |
| 3. | Quotation | 10 |  |  | 23/1/2021 |
| 4. | Invoice |  | 456.00 | 126.59 | 5/2/2021 |
| 5. | Invoice |  | 126.59 | 56.70 | 23/2/2021 |

Answer: 
You may try a union query:

```
$first = DB::table('Invoice')
    ->select('description', NULL, 'price', 'balance', 'invoice_date')
    ->where('invoice_date', '>=', '2021-01-01')
    ->where('invoice_date', '<', '2021-03-01');

$result = DB::table('Quotation')
    ->select('description', 'qty', NULL, NULL, 'quo_date')
    ->where('quo_date', '>=', '2021-01-01')
    ->where('quo_date', '<', '2021-03-01');
    ->unionAll($first)
    ->get();

```

This corresponds to the following raw MySQL query:

```sql
SELECT description, NULL, price, balace, invoice_date
FROM Invoice
WHERE invoice_date >= '2021-01-01' AND invoice_date < '2021-03-01'
UNION ALL
SELECT description, qty, NULL, NULL, quo_date
FROM Quotation
WHERE quo_date >= '2021-01-01' AND quo_date < '2021-03-01';

```
Since eloquent, what's the relationship between Quote and Invoice?
Would it be something like 1 quote will have many invoice? And I guess a Quote comes before an invoice.
if it is.

1. You will need to have to add another column in Invoice. so Quotation\_id
2. Create your Quotation Model

> 
> 
> ```
> class Quotation extends Model
> 
> {
>     use HasFactory;
> 
>     protected $fillable = [
>         'qty',
>         'description',
>         'quo_date'
>     ];
> 
>     public function Invoice()
>     {
>         return $this->hasMany(Invoice::class);
>     } }
> 
> ```
> 
> 

3.Create your Invoice Model

```
    class Invoice extends Model
    {
    use HasFactory;

    protected $fillable = [
        'price', 'description', 'invoice_date', 'subtotal' 
    ];

    public function Quotation() 
    {
         return $this->belongsTo(Quotation::class);
    }
}

```

4. Next in your Controller

```

    use App\Models\Invoice;
    use App\Models\Quoatation;

    $query = Quoatation::wherebetween('quo_date', [2021-01-01,2021-03-01])
    ->orderby('quo_date', 'ASC')->with('Invoice')
    ->wherebetween('invoice_date', [2021-01-01,2021-03-01])
    ->orderby('invoice_date', 'ASC')
    ->get();

```