I want to use Laraveloquant's with to make the column of the destination table a where condition.

·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

laravel

2022-09-30 20:54

1 Answers

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);
    }
}

2022-09-30 20:54

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656