I have a relationship between your_electricity_yesterday_category and building as building_id is present in your_electricity_yesterday_category table.

I am trying to get details out of the building table using the relationship.

I have this in my Electricity model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Electricity extends Model
{
    use HasFactory;
    protected $connection = 'mysql2';
    protected $table = 'your_electricity_yesterday_category';

    public function buildings()
    {
        return $this->belongsTo(Building::class, 'building_id');
    }
}

I have this in my Repository

public function getAllBuilding()
    {
        // $buildings = Building::where('module_electricity', 1)->orderBy('description')->get();

        $buildings = Electricity::with('buildings')->get();


        return $buildings;
    }

I have this in my controller

public function electBuilding()
    {
        $getBuilding = $this->electricityRepository->getAllBuilding();
        
        return response()->json($getBuilding);
    }

On the building table i have a column where module_electricity is either 0 or 1

How can i use this relationship to return building where module_electricity is 1 in json?

CodePudding user response：

use whereHas query builder to filter parent Electricity details based on condition

$buildings = Electricity::with(['buildings'=>function($query){
        $query->where('module_electricity',1);
      }])
      ->whereHas('buildings',function($query){
        $query->where('module_electricity',1);
      })->get();

Also you can write scope for where condition in buildings model like below

public function scopeModuleElectricity($query,$module){

   return $query->where('module_electricity',$module);
}

so your query will be

$buildings = Electricity::with(['buildings'=>function($query){
        $query->moduleElectricity(1);
      }])
      ->whereHas('buildings',function($query){
        $query->moduleElectricity(1);
      })->get();

CodePudding user response：

Here is what I came up with:

public function getAllBuilding()
{
    return Electricity::query()
        ->with('buildings', fn ($query) => $query->where('module_electricity', 1))
        ->get()
        ->pluck('buildings')
        ->collapse();
}

Walking through this step by step so you can better understand what's happening:

1. Initiating a query (completely optional, just for better code formatting)
2. Eager loading buildings with a condition (module_electricity = 1)
3. Retrieving data from the database
4. Extracting buildings only
5. Flat-mapping results

This will return a single collection with buildings that met a condition.

Let me know if the result turned out to be exactly what you expected.

P.S. Note that the above solution might not work if you're using older versions of PHP. If the above returns syntax error:

replace:

fn ($query) => $query->where('module_electricity', 1)

with:

function ($query) {
    $query->where('module_electricity', 1)
}