Merging two tables using Eloquent (Without using DB::Table)

Question

I have two models, Event and CoverEvent. A cover event can only be linked to one event. I was able to do it using SQL. But I'd like to have it in a clean manner, using Eloquent. How should I do this?

Many thanks!

Here's the SQL:

SELECT
    `cover_events`.`id`,
    `events`.`slug`,
    `events`.`title`,
    `events`.`subtitle`,
    `events`.`image`,
    `cover_events`.`columns`,
    `cover_events`.`right_align`,
    `cover_events`.`dark_text`,
    `cover_events`.`large`
FROM
    `cover_events`,
    `events`
WHERE
    `is_visible` LIKE 'true' AND
    `cover_events`.`event_id` = `events`.`id`

Which should result as the following array:

[
    [
        "id" => 1,
        "slug" => 'celine-dion-2020',
        "title" => 'Celine Dion',
        "subtitle" => '31 July 2020',
        "image" => env('APP_URL') . '/img/celine_dion.jpg',
        "columns" => 8,
        "right_align" => false,
        "dark_text" => true,
        "large" => true
    ],
    ...
]

Jithesh Jose · Accepted Answer · 2019-11-29 13:36:24Z

1

In eloquent you can only use relationships to join tables..

In CoverEvent class use a relationship

public function events()
{
    return $this->hasOne('App\Models\Event', 'id', 'event_id');
}

Retrieve the result using

$res = CoverEvent::where('is_visible','true')->get();

foreach($res as $cover_event)
{
  $cover_event->events->slug;  // data from event class
  $cover_event->columns;    // data from cover_event class
}

edited Nov 29, 2019 at 13:36

answered Nov 29, 2019 at 13:30

Jithesh Jose

1,8241 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jose Andraos Over a year ago

Thanks for the help! I tried out your code, but it's creating an array within array. I'd like to have the events array part of the main array, as one, not an array within an array. How could I do this? Thanks again, I appreciate it!

Jithesh Jose Over a year ago

You can't join tables in eloquent without using relationship.If you want the result in one single array you have to use query builder.

Khusal Berva · Accepted Answer · 2019-11-29 13:59:42Z

1

for example if your parent model is ManageClass and child modal is DaysClassDetails :-

class ManageClass extends Authenticatable
{   
   public function classType()
   {
      return $this->hasMany('App\DaysClassDetails','day_id','id');
   }
}

and enter this command in your controller function

ManageClass::with('classType')->get();

answered Nov 29, 2019 at 13:59

Khusal Berva

1345 bronze badges

Comments

Pavel Lint · Accepted Answer · 2019-11-29 13:28:47Z

0

You could use Laravel's collection merge() method:

$events = Event::all()->merge(CoverEvent::all());

answered Nov 29, 2019 at 13:28

Pavel Lint

3,6071 gold badge20 silver badges18 bronze badges

Collectives™ on Stack Overflow

Merging two tables using Eloquent (Without using DB::Table)

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related