获取另一个表中不存在的所有项目

Question

我正在使用Laravel Framework 6.16.0并且有两个表，logos并且companies：

标志：

        Schema::create('logos', function (Blueprint $table) {

            $table->bigIncrements('id');

            $table->integer('companies_id')->default('999999');

            $table->string('logo_path')->nullable($value = true); // path to the logo image

            $table->timestamps();

        });

公司：

        Schema::create('companies', function (Blueprint $table) {

            $table->bigIncrements('id');

            $table->string('symbol'); // AAPL

            $table->string('name'); // company name such as Apple Inc.

            $table->timestamps();

        });

楷模：

class Logo extends Model

{

    protected $table = 'logos';

    protected $guarded = ['id'];

    public function company()

    {

        return $this->belongsTo(Company::class, 'companies_id');

    }

}

class Company extends Model

{

    protected $table = 'companies';

    protected $guarded = ['id'];

}

我想获取 中没有徽标logo-table且徽标尚未更新的所有符号90 days。

我尝试了以下方法：

        $symbolsWithoutLogos = DB::table('companies')

            >leftJoin('logos', 'companies.id', '=', 'logos.companies_id')

            ->whereDate('logos.updated_at', Carbon::now()->subDays(90))

            ->whereNull('companies.logo_image')

            ->orderBy('name', 'ASC')

            ->get('companies.*');

该查询的结果是：

select `companies`.* from `companies` left join `logos` on `companies`.`id` = `logos`.`companies_id` where date(`logos`.`updated_at`) = ? order by `name` asc

但是，即使我的公司表有足够的记录，运行此查询时我也得不到结果：

有什么建议我做错了什么吗？

我很感谢你的回复！

Answer 1

您可以使用 Eloquent 来实现此目的，而不是使用左连接和数据库外观。我猜公司和Logo之间的关系是1:1或1:n。如果我们假设它是 1:1 那么你的查询将如下所示：Company::whereHas('logo', function($query) {    return $query->where('date', '<', Carbon::now()->subDays(90));})->orWhereDoesntHave('logo')->...您应该检查：查询关系缺失只要我们遵循 Laravel 命名约定，我们就不需要指定表名，Laravel 会从模型名称中找出它。标志模型应该是这样的：class Logo extends Model{    // protected $table = 'logos';     // not necessary, Laravel already knows to use logos table    protected $guarded = ['id'];    public function company()    {        // return $this->belongsTo(Company::class, 'companies_id');        // no need to specify foreign key if we follow naming convention        // foreign key should be {foreign_model_singular}_id        // in this case it is company_id        return $this->belongsTo(Company::class);    }}Company 模型也不需要指定表名，它已经知道它应该是Companies。公司模式：class Company extends Model{    // protected $table = 'companies';    protected $guarded = ['id'];    public function logo()    {        return $this->hasOne(Logo::class);    }}请检查 $guarded 和 $fillable 属性如何工作。如果只有id设置为受保护，则所有其他属性均不受保护，不确定这是否是您的意图。完整的查询应该是：Company::whereHas('logo', function($query) {    return $query->where('updated_at', '<', Carbon::now()->subDays(90));})->orWhereDoesntHave('logo')->orderBy('name', 'ASC')->get();

Answer 2

->whereDate('logos.updated_at' whereDate 仅比较 dateTime 列的日期部分 ... 尝试使用 Carbon 的 toDateString() ...&nbsp; $symbolsWithoutLogos = DB::table('companies')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->leftJoin('logos', 'companies.id', '=', 'logos.companies_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereDate('logos.updated_at', Carbon::now()->subDays(90)->toDateString())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereNull('companies.logo_image')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->orderBy('name', 'ASC')->select('companies.*')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->get();