Integrity constraint violation while passing query builder to DataFilter

[tartarJR]

I am trying to join 3 tables and passing to a DataFilter as data source as

    public function createSearchFilter()
    {
        $datasource = DB::table('vehicles')->join('brands', 'vehicles.brand_id', '=', 'brands.id')->join('sections', 'vehicles.section_id', '=', 'sections.id');
        $searchFilter = DataFilter::source($datasource);
        $searchFilter->attributes(array('class'=>'form-inline'));
        $searchFilter->add('vehicles.id','Document Number', 'text')->attr('class','textbox');
        $searchFilter->add('licenseplate','License Plate', 'text')->attr('class','textbox');
        $searchFilter->add('brand','Brand', 'text')->attr('class','textbox');
        $searchFilter->add('model','Model', 'text')->attr('class','textbox');
        $searchFilter->submit('Search');
        $searchFilter->reset('Reset');
        $searchFilter->build();

        return $searchFilter;
    }

But when i try to do a search i am getting this error.

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select count(*) as aggregate from vehicles inner join brands on vehicles.brand_id = brands.id inner join sections on vehicles.section_id = sections.id where id LIKE %1% and licenseplate LIKE %09%)

How can i specify to necessary id column(which is vehicles.id) for the query ? Any help would be appreciated.

[zofe]

you're doing a join but you're not using related table. This is a bit wrong. you can use something line ->select('vehicles.*, vehicles.id AS vid') then you can use a filter on vid.

But i just suggest you alternatives, define the relation in your model, use a ::with and then yo're able to show related fields

[tartarJR]

Thanks for your suggestion, will try.

[tartarJR]

i defined the necessary relations in my model and trying to pass the data source to DF as

$datasource = Vehicle::with('brand','section');

Still not able to reach to data. I tried to echo the data source and it is working well but i can show related fields. What am i doing wrong ?

    public function createSearchFilter()
    {
        $datasource = Vehicle::with('brand','section');
        $searchFilter = DataFilter::source($datasource);
        $searchFilter->attributes(array('class'=>'form-inline'));
        $searchFilter->add('id','Document Number', 'text')->attr('class','textbox');
        $searchFilter->add('licenseplate',Llicense Plate', 'text')->attr('class','textbox');
        $searchFilter->add('brand_name','Marka', 'text')->attr('class','textbox');
        $searchFilter->add('model','Model', 'text')->attr('class','textbox');
        $searchFilter->submit('Search');
        $searchFilter->reset('Reset');
        $searchFilter->build();

        return $searchFilter;
    }

[zofe]

before asking this.. did you checked this sample: http://www.rapyd.com/rapyd-demo/filter Because it show how you can build a filter from a entity + "with" relations, and show related fields. About how to "filter" by a related field.. a datafilter just extend a dataform so you can use a field with this name/notation: 'relation.field'.

Filter can also works with custom fields and custom queries as explained here: http://www.rapyd.com/rapyd-demo/customfilter

[mhanoglu]

$pins = DB::table('pins')
        ->select("pins.*","pin_titles.amount")
        ->where('pins.dealer_id', Auth::user()->getDealerID() )
        ->leftJoin('pin_titles','pin_titles.id','=','pins.pin_title_id');

I've change leftJoin query to model:with('model) $pins = Pin::with('pin_title')->where('dealer_id',Auth::user()->getDealerID()); And my pin model

class Pin extends Model
{
    protected $table = 'pins';
    public function pin_title()
    {
        return $this->belongsTo('App\PinTitle');
    }
}

Problem solved. Thanks.