I would suggest an option of having a single offers table the have a offer type column. This assumes the offers have similar information where they could saved together. Otherwise in the current three tables, just do a select from each table and merge the results into a single array/object.

they all have different information but only some columns are same (id,title,created_at,updated_at)

but selecting from each table and merging into single may not be efficient as i want to show only 3 latest offers. is there any efficient way to handle it, so that it will only fetch latest 3 rows from all tables.

You may need to make sure each table has a row "created_at", you can then query each table in DESC order and limit by 3.

High Level Example:

$ppc_offers_latest = ModelName::orderBy('created_at', 'desc')->limit(3)->get();

$survey_offers_latest = ModelName::orderBy('created_at', 'desc')->limit(3)->get();

$find_offers_latest = ModelName::orderBy('created_at', 'desc')->limit(3)->get();

If you are using Eloquent, in theory this would make three calls to the DB and return you the last three offers created in the table. Of course, you can change the limit number in order to return the actual amount per table needed.

Hope this points you in the right direction.

Eloquent Docs: http://laravel.com/docs/eloquent

but that will fetch 9 items and i want only 3 items 'latest` irrespective of tables.

I see what you are saying...

I think (and I could be wrong) you are going to have to do 3 queries no matter what. The amount of data you want back is so minimal, this should be ok. You can then do what I am showing above (returns 9 results) but then in your controller or class you create to handle it, build some logic to compare the 9 and return only the latest 3 in an array back to the view.

It would be possible to do it in a single query if you use the query builder's union.

http://laravel.com/docs/queries#unions

But unfortunately, I'm not knowledgeable enough in laravel to build a sample code of this. But I'm sure that this is what you're looking for.

I assume all 3 tables do have id, title and created_at columns. This database query should fetch the most fresh offer titles no matter from which table they come from. Note however that id's of each row will be the ones of individual tables and therefore it can be tricky to distinguish them. Nonetheless this should work (on MySQL).

This is a sample route for you to test:

Route::get('test',function(){
    $freshOffers = DB::select('
    (select `id`, `title`, `created_at` from `ppc_offers`)
    union all
    (select `id`, `title`, `created_at` from `survery_offers`)
    union all
    (select `id`, `title`, `created_at` from `find_offers`)
    order by `created_at` desc
    limit 3
    ');
    echo '<pre>' . print_r($freshOffers,true) . '</pre>';
});

pickupman said:

I would suggest an option of having a single offers table the have a offer type column. This assumes the offers have similar information where they could saved together. Otherwise in the current three tables, just do a select from each table and merge the results into a single array/object.

Totally agree. However you manage to solve the problem with your current DB structure is likely going to be a bit of a hack (I'm guessing, sounds like it). What you're going through now is probably a symptom of a poor design. Ideally you should normalize the "offers" into their own table and order by created_at, limit 3, problem solved.

Your database schema is fine. If you need to keep those tables logically seperate the best way to do this is to create a database view and then select from it.( a view allows you to combine many tables if needed and treats it as a single table)

Use this as your view definition, (you only have to run the create statement once):

CREATE VIEW global_specials AS
(select `id`, `title`, `created_at` from `ppc_offers`)
union all
(select `id`, `title`, `created_at` from `survery_offers`)
union all
(select `id`, `title`, `created_at` from `find_offers`)

Then a simple query on the view will give you exactly what you are looking for, every time.

select * from global_specials order by created_at limit 3