Select fields in complex with() queries

Thank you for your quick reply, but that's not working. The fields from the sub-tables (like TableDEF) are not selectable with this method. Also not with a prefix like "TableDEF.fieldname"

Sorry to late :) not reading ... this too much, i suspect someone has an easier solution, but while you are waiting:

class ComplexWhere {

    public function scopeSearchByAssociations($query, $value, $criterea)
    {
        if ( ! is_array($value)) return $query;

        $value = array_filter($value, 'strlen');

        if ( empty($value) ) return $query;

        $query = $query->select('product.*')->join('product_association',
            'product_association.product_id', '=', 'product.id'
        );

        foreach ($value as $id => $valueID)
        {
            $query = $query->searchByAssociation( $id, $valueID );
        }

        return $query;
    }

    public function scopeSearchByAssociation($query, $id, $valueID)
    {
        $alias = sprintf( "pav_%s_%s", $id, $valueID );

        return $query->join("product_association_value as {$alias}",
            function( $join ) use ( $alias, $id, $valueID )
            {
                $join->on("{$alias}.product_association_id", '=', 'product_association.id')
                    ->on("{$alias}.association_id", '=', \DB::raw($id) )
                    ->on("{$alias}.association_value_id", '=', \DB::raw($valueID) );
            }
        );
    }
}

Hello nolros,

thanks for your idea. I also worked with joins, but i figured out, that i have to use them in every function. So i decided to try the "eloquent way" and it is working very nicely ... but the field selection. Laravel returns me a multidimensional array, which is perfect and i can use it very well ... but: All fields (> 100, because my selection is a little bit longer than in my example) are listed.

Within that query i need data from tables, five levels deeper than the main table:

Table1 > Table2 > Table3 > Table4 > Table5.fieldname

The structure of the database if quite fine and i can easily store all informations. With joins i can read those data ofcourse ... but then i have the complex joins every time ;o)

Hello,

To select in an eager loading you have to add a select like that :

$posts = Post::with(['author' => function($q)
{
    $q->addSelect(['town', 'name']);
}])->get();

Hello bestmomo,

thank you for your help. I tried that method, but now i get no fields back for that subtable/subarray. So currently i can only "select" between all fields and no fields.

The fieldnames are correct - i double checked it twice.

I also played around with that array-function within the "with" call. But also the normal $query->select("fieldname") is not working for those subtables. In normal queries everything works fine.

*Edit:

I tried also the sub-sql-statement: I misspelled a column name and the laravel-debug-error window was shown. From that i copied the SQL statement to phpMyAdmin, corrected the columnname and executed it: Several records were found. Correcting the column name again in the "with()" statement/function, i got an empty subarray again.

Another way is to include the select in relation :

public function authors()
{
    return $this->hasMany('Author')->select(['town', 'name']);
}

Thank you for your info.

The problem is: With that way i will always (!) have only those fields in my relations / results. But for the most queries i need all details - and then select the fields i need in the current request.

@jugi

You can make many relation methods, one with all fields, one with a select...

OK. I know, but ...

... then i have to create several Relations for my special selections.

There should be a way to adjust the returned fields directly within the selection ... not over the relationsway. I'm trying to make my apps also "nice to develop" in the backend. When i have to change a field in 6 or 10 month, it's easier to go into the selecion and add the field needed - instead of f.eg. create a new relation because i might destroy another selection/relation with my changes.

You ask too much for eloquent. And this is not a big deal to select all the fields.

@bestmomo why $q->addSelect() and not just $q->select() ?

@pmall good question ! I dont really remember why but $q->select() should be enough.

Yes I was wondering because the with method with callback to select some fields should work for the original question.

Maybe the select have to include the foreign keys ? Because the eager loading mechanism needs foreign keys to map the related objects to the parent object.

@pmall I made a test and you're right, needs the foreign key in select to work. So code is rather :

$posts = Post::with(['author' => function($q)
{
    $q->select(['town', 'name', 'post_id']);
}])->get();

YES !!

Thank you a lot. That was the solution: A foreign key in the selection and it is working. I votet the initial idea from pmall as my favorite solution, but thank you of couse also to @bestmomo. Your posts with small pieces of code are also very helpful !

@pmall (response with [...] You ask too much for eloquent [...])

How can i ask too much ? I think this is a forum. Or do you mean i expect too much from laravels eloquent ?

Yes you expect a litte bit much from eloquent orm but there was an answer :)

I suggest you add scopes to your eloquent models for each use cases so you don't type with('...', function(){ ... }) everywhere.