How to get a query instance of two differents tables (not a Collection), join two different tables?

I wouldn't get all unless necessary, what if it grows to millions of records.

If only a small amount, you can paginate a collection: https://laracasts.com/discuss/channels/guides/paginate-collection-simple-example-guide

See last example, not first.

I don't want to show all the records, i want to get all records and paginate it, can it be a problem?

If you are doing some other operation, use chunk:

https://laravel.com/docs/8.x/queries#chunking-results

This is what i need:

I have two tables: Payments and Expenses. I want to merge all the records from these tables and show in the same page, these tables have similar columns but not equals, i could deal with the different columns not problem with that.

Here is what i did:

$entries = Expense::all();
$entries = $entries->merge(Payment::all());

So now i have a array of models and i can sort by date (both columns have a 'date' column)

$entries->sortByDesc('date')

So i'm using a a helper to paginate the records from a collection

return view('cash-flow.index', [
    'entries' => CollectionHelper::paginate($entries->sortByDesc('date'), 10)
]);

Everything works fine, the only thing i would like to know is if it's possible to do it with a QueryBuilder instance, for example, join these two tables and sorte by date, paginate these records and show, just to make the code cleaner idk.

In my view i can distinguish when it's a Payment or Expense with a if:

@foreach ($entries as $entry)
@if ($entry instanceof \App\Models\Payment)
	// It's a payment
@else
	// It's a expense
@endif
@endforeach

If I understand you correctly, you have two tables that have something similar fields and you want to get records from both ordered by a certain column?

Something like this.

Records

• id
• title
• artist
• created_at

Books

• id
• title
• author
• created_at

And now you want the ten latest additions to those two tables.

SELECT id, title artist creator, 'records' type FROM records
UNION ALL
SELECT id, title author creator, 'books' type FROM books

This query gets every thing from both tables.

Then you wrap them with another select like this

SELECT *  FROM
(SELECT id, title artist creator, 'records' type, created_at FROM records
UNION ALL
SELECT id, title author creator, 'books' type, created_at FROM books)
ORDER BY created_at DESC
LIMIT 10

Here's how to do it with Querybuilder

https://laravel.com/docs/8.x/queries#unions

Unfortunatelly the tables has not the same number of columns, i tried it before and got this error.

That doesn't matter if you tell it to get the same number of fields.

So if the first one has ten fields and the other just eight you can either just get 8 from both or you can add null fields to the second select.

SELECT *  FROM
(SELECT id, title artist creator, 'records' type, created_at, some_other_field FROM records
UNION ALL
SELECT id, title author creator, 'books' type, created_at, null some_other_field FROM books)
ORDER BY created_at DESC
LIMIT 10

I didn't realize that in-depth of a instruction was needed sorry.

I would highly suggest you go here https://www.mysqltutorial.org/

And browse around they have all types of query examples.

I just thought since you was already using laravel you were already familiar with SQL.

@tray2 I think that is exactly what i need, thanks.

@jlrdw Thank you, this will be helpful, i know SQL but not so much, i know how most of things work but i'm not good to write SQL queries.