·Table Configuration
The item is the article table and the channel is the site table.
The channel versus item is one-to-many.
item:article table
channel:site table
What I want to do is to get articles for each category.
Currently, we are able to obtain the following code using left join, but
In this situation, it will take a long time to retrieve.
$items=Item::leftJoin('channel', 'item.channel_id', '=', 'channel.id')
->select(
'item.id',
'item.title',
'item.description',
'item.link',
'item.pub_date',
'item.channel_id',
'channel.title as channel_title',
)
->orderBy('pub_date','desc')
->where('category_id', '=', $category->id)
->paginate(200);
Therefore, I am thinking of acquiring it using with.
$items=Item::with('channel')
->select(
'item.id',
'item.title',
'item.description',
'item.link',
'item.pub_date',
'item.channel_id',
'channel.title as channel_title',
)
->orderBy('pub_date','desc')
->where ('channel.category_id', '=', $category->id)
->paginate(200);
However, with does not have a category_id in the item, so
->where('channel.category_id', '=', $category->id)
is not available.
Could you tell me if there is a smart way?
The table configuration is as follows:
channel table
CREATE TABLE `channel`(
`id`int(10) unsigned NOT NULL AUTO_INCREMENT,
`title`varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`description`varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`link`varchar(255)COLLATE utf8_unicode_ci DEFAULT NULL,
`rss_link`varchar(255)COLLATE utf8_unicode_ci NOT NULL,
`sort`int(11) NOT NULL,
`category_id`int(11) NOT NULL DEFAULT '0',
`created_at`timestamp NULL DEFAULT NULL,
`updated_at`timestamp NULL DEFAULT NULL,
`deleted_at`timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8COLATE=utf8_unicode_ci
item table
CREATE TABLE `item`(
`id`int(10) unsigned NOT NULL AUTO_INCREMENT,
`title`varchar(255)COLLATE utf8_unicode_ci NOT NULL,
`description`varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
`link`varchar(512)COLLATE utf8_unicode_ci NOT NULL,
`pub_date`datetime NOT NULL,
`channel_id`int(11) NOT NULL,
`created_at`timestamp NOT NULL DEFAULT'0000-00:00:00:00',
`updated_at`timestamp NOT NULL DEFAULT'0000-00:00:00:00',
`deleted_at`timestamp NULL DEFAULT NULL,
PRIMARY KEY(`id`),
KEY`channel_id`(`channel_id`)
ENGINE=InnoDB AUTO_INCREMENT=183691 DEFAULT CHARSET=utf8COLATE=utf8_unicode_ci
Why not combine whereHas()?
//Assume an Eloquent object for the Category table
$category=(object)['id'=>1];
$with=function($q)use($category){
$q->select([id', 'title']->where('category_id', $category->id);
};
$whereHas=function($q)use($category){
$q->where('category_id', $category->id);
};
$columns=[
'id',
'title',
'description',
'link',
'pub_date',
'channel_id',
];
$items=\App\Item::with (['channel'=>$with])
->whereHas('channel', $whereHas)
->select ($columns)
->orderBy('pub_date','desc')
->paginate(200);
The following Eloquent classes were used for validation:
\App\Channel
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class channel extensions Model
{
protected$table='channel';
public function items()
{
return$this->hasMany(Item::class);
}
}
\App\Item
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Item extensions Model
{
protected$table='item';
public function channel()
{
return $this->belongsTo (Channel::class);
}
}
© 2024 OneMinuteCode. All rights reserved.