Question 109600 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Unable to fetch data from relational table using laravel with() method

I have table "Jobs" having a field called "empids". we are storing comma separated employee ids in this field, for ex. 1,12,56. I am trying to return JSON using with() method by making belongsTo relation ship between Job and employee models. There are more relation ship tables working fine with Jobs table using belongsTo as they are perfectly matching forien_key and index_key.

Below is my laravel code

// Get Job function:
       $emps = job::where('id',$id)->where('compid',$compid)->pluck('empids');
        $jobs = job::with(['jobcustomer'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }])
        ->with(['jobcustomeraddress'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }])
        ->with(['jobdetails'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }])
        ->with(['jobemps'=>function ($q) use ($emps) { $q->whereIn('id',$emps); }])
        ->where('id',$id)->where('compid',$compid)->orderBy('id')->get();
        return $jobs;

//In Job Model:
public function jobemps()
    {
        return $this->belongsTo(employee::class);
    }

Below is what i am receiving as JSON. "Jobemps" are null

[
    {
        "id": 71,
        "invid": "3",
        "segid": "9",
        "segname": "segment # 9",
        "due": "Upon start of work",
        "empids": "1,2,12",
        "invcurrstatus": null,
        "customer_id": 7,
        "custaddresse_id": 48,
      
        "jobcustomer": {
            "id": 7,
            "fname": "fawaz",
            "lname": "chughtai",
            "dname": "lat long",
            "email": "[email protected]",
        },
        "jobcustomeraddress": {
            "id": 48,
            "customer_id": 7,
            "street": "82 NJ-23",
            "city": "Hamburg",
            "state": "NJ",
            "zip": "07419-1400",
            "unit": null,
        },
        "jobdetails": [
            {
                "id": 351,
                "job_id": 71,
                "invid": "3",
                "segid": "9",
                "itemsec": 1,
                "invitem_id": 1,
                "itemname": "Service Call",
                "itemunit": "Each",
                "itemcost": 0,
                "itemprice": 69,
                "itemqty": 1,
                "itemtotal": 69,
            },
            {
                "id": 352,
                "job_id": 71,
                "invid": "3",
                "segid": "9",
                "itemsec": 2,
                "invitem_id": 2,
                "itemname": "Repair - Unclog toilet",
                "itemdesc": "Unclog a toilet. Must be reasonably accessible.",
                "itemunit": "Each",
                "itemcost": 0,
                "itemprice": 71,
                "itemqty": 1,
                "itemtotal": 71,
            }
        ],
        "jobemps": null
    }
]

I am looking for jobemps as:

"jobemps": [
            {
                "id": 1,
                "name": "abc",
            },
            {
                "id": 12,
                "name": "efg",
            },
            {
                "id": 56,
                "name": "xyz",
            }
        ],

Error i am receiving is select * from employees where 0 = 1 and id in (1,2,12)

I dont know how to remove where 0 = 1
Please guide, Thanks

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    OK So based on Pemba Tamang's answer, it does solved the issue, here is the 100% working code with little fixes in Pemba's code

    $emps = (job::where('id',$id)->where('compid',$compid)->first())->empids;
        $emps = explode(',',$emps);
        
        $jobs = job::with(['jobcustomer'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }])
        ->with(['jobcustomeraddress'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }])
        ->with(['jobdetails'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }]) 
        ->where('id',$id)->where('compid',$compid)->first();
        

        $jobemps = employee::whereIn('id',$emps)->get();
        $data['job'] = $jobs;
        $data['jobemps'] = $jobemps;
        return $data;

    Now i dont know if I should Mark Pemba's answer as accepted or mine, or may be Pemba corrects his answer so that I mark his answer as accepted. Thanks Pemba


  2. 0 
    //In Job Model:
public function jobemps()
{
    return $this->belongsTo(employee::class, null);
}

    Change your relationship to above, what it does is that it makes your relationship optional.

    The belongsTo method generates a SQL statement that retrieves the related model using a join query. The SQL statement includes a condition that links the foreign key of the current model to the primary key of the related model. However, if the foreign key value is null, the join query will not produce any results.

    In some cases, the belongsTo method may generate a SQL statement that includes a condition where 0 = 1. This happens when the foreign key value is not set or is set to null, and the relationship is set to not be optional. In this case, the join query is designed to not return any results because the related model is required and the foreign key is not set.

    Login or Signup to reply.
  3. 0

    I think there doesnot exist relationship between job and employees according to your table structure. so creating relationship function is of no use.
    $emps = job::where(‘id’,$id)->where(‘compid’,$compid)->pluck(’empids’);
    convert $emps result to array.

    since you want the query result attached with jobs u can attach an array like this

    $emps = job::where('id',$id)->where('compid',$compid)->pluck('empids')->toArray();
        $jobs = job::with(['jobcustomer'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }])
        ->with(['jobcustomeraddress'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }])
        ->with(['jobdetails'=>function ($q) use ($compid){
            $q->where('deleted_at',null)->where('compid',$compid);
        }]) 
        ->where('id',$id)->where('compid',$compid)->first();
        

$jobemps=Employee::whereIn('id',$emps)->get();
$data['data'] = $jobs;
$data['jobemps'] = $jobemps;
return $data;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search