Relationships problem

[jokeronaldo]

Summary of problem or feature request

Relationships are outputting stranger SQL, just does not work. Why it's writting IS NULL and IS NOT NULL over the query?

Code snippet of problem

MAIN MODEL

<?php
namespace Models;

use Illuminate\Database\Eloquent\Model as Eloquent;

class Tgfpro extends Eloquent
{
    protected $table = 'TGFPRO';
    protected $primaryKey = 'CODPROD';
    public $timestamps = false;

    public function valor()
    {
    	return $this->hasMany('Models\Tgfexc', 'CODPROD')
    		->select('VLRVENDA');
    }

    public function getImagemAttribute($value)
    {
    	return base64_encode($value);
    }
}

SECONDARY MODEL

<?php
namespace Models;

use Illuminate\Database\Eloquent\Model as Eloquent;

class Tgfexc extends Eloquent
{
    protected $table = 'TGFEXC';
    public $timestamps = false;
}

CONTROLLER CALL

        $produtos = Models\Tgfpro::where('ATIVO', 'S')
        	->limit(10)
        	->orderBy('CODPROD', 'ASC')
        	->get([
        		'CODPROD',
        		'DESCRPROD',
        		'IMAGEM'
        	]);

OUTPUTTED SQL

select "VLRVENDA" from "TGFEXC" where "TGFEXC"."CODPROD" is null and "TGFEXC"."CODPROD" is not null

System details

• Windows 10
• PHP 5.6.30
• Using stand alone
• Laravel-OCI8 ^5.4

[jokeronaldo]

PS: When I try to access the list of relation items it returns empty.

[mstaack]

maybe your primary keys are different than defaults...

looks like its a problem on your side

[jokeronaldo]

I had informed the primary key in model definition and the foreign key on hasMany. It's not the problem.

[mstaack]

@jokeronaldo the definition for this relationship is: return $this->hasMany('App\Comment', 'foreign_key', 'local_key');

you have a select there also...why? put that to the calling place not in the relation defintion

[jokeronaldo]

@mstaack If you look my MAIN MODEL, where I described my code, I point the following:

return $this->hasMany('Models\Tgfexc', 'CODPROD')

Where CODPROD is my foreign key and the last attribute is my local key, as I mentioned at main model code

protected $primaryKey = 'CODPROD';

Anyway, it's not the problem. The problem actually is something on query builder, if you look my sql output result you could see an ambiguous select is null and is not null.

[mstaack]

yeah i have seen your sql output...but what about this:

    public function valor()
    {
    	return $this->hasMany('Models\Tgfexc', 'CODPROD')
    		->select('VLRVENDA');
    }

select() shouldn't be there....

[jokeronaldo]

@mstaack select should be there if you wnat, it's not a problem, i've tried with out it and I got same result.

About foreign_key, local_key, both got same name in both tables. Well, in fact it's a clue. I will try to alias the columns, but I doubt is that.

[mstaack]

@jokeronaldo ok sry then... for me relations work fine so far ;)

[jokeronaldo]

@mstaack Thank you anyway =) I'm working on a legacy project, there's no id columns, its based on compound columns and the database map isn't human readable.

[mstaack]

uhhh..missing primary keys and compound columns....that will create problems ;)

[yajra]

@jokeronaldo you might be using a global scope somewhere in your code that adds the null sql?

[jokeronaldo]

@yajra nops.

[fabriciopaulo]

I have exactly the same problem, when the primary key is not integer, the sql is generated with 'is null' or 'is not null'! My relationships are correctly defined according Laravel documentation

[jokeronaldo]

@fabriciopaulo I gave up to fix the problem, instead using relations, I done some black magic in code. Serious, I tried many different ways, nothing happens, nothing works.

[ChaosPower]

I do not get this issue, I have a project which has many existing tables. And primary keys are non integers. Probably a defect with 5.6 ? Will need to test your snippets to confirm.

Can you send here your create table query / DDL output?

Thanks

[manhcuong2903]

i have a problem with relation hasMany public function posts(){ return $this->hasMany(Post::class,'LINKID','USERID'); }

when i run this code it return me LINKID = null $user->posts()->saveMany($data); @yajra

[paulosscruz]

@jokeronaldo were you able to find the solution to this problem?

I am also having problems with the relationship between keys that are not integers.

[jokeronaldo]

@paulosscruz I've left behind relations and do a normal query

[manhcuong2903]

@jokeronaldo it's not a good performance for your query if you don't use relation

[goten60]

Hi, I got the exact problem,

Primary Model

<?php

namespace App\Modelos;

use Yajra\Oci8\Eloquent\OracleEloquent as Eloquent;

class Persona extends Eloquent
{
	protected $table = 'AI_PERSONA';
	protected $primaryKey  = 'PER_ID';
	public $timestamps = false;
	
	public function liceos()
	{
		return $this->hasMany('App\Modelos\Liceo','PER_ID');
	}
}

Second Model

<?php

namespace App\Modelos;

use Yajra\Oci8\Eloquent\OracleEloquent as Eloquent;

class Liceo extends Eloquent
{
    //
	protected $table = 'AI_RBD';
	protected $primaryKey = 'RBD_ID';
	public $timestamps = false;
}

Example en web.php

Route::get('test2', function() {	
	$persona = Persona::where('PER_ID',10)->with('liceos')->first();	
	dd ($persona);
});

Query ouput:

[2018-11-05 10:31:48] local.INFO: select * from (select * from "AI_PERSONA" where "PER_ID" = ?) where rownum = 1
[2018-11-05 10:31:48] local.INFO: array ( 0 => 10, )
[2018-11-05 10:31:48] local.INFO: select * from "AI_RBD" where "AI_RBD"."PER_ID" in (?)
[2018-11-05 10:31:48] local.INFO: array ( 0 => NULL, )

Somehow the Key is lost. I make another example in mysql and works fine.

System details

• Windows 10
• PHP 7.1.22
• Using stand alone
• Laravel 5.5.*
• Laravel-OCI8 ^5.5.*

Problem solved, All the name of tables, camps, id, etc where change to lowercase, this solved the problem.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.