Retrieving multi-table data through the query builder

I'm running a query with a join in it, such that when there are two conflicting columns (for example: articles.status and authors.status) only one of the columns is output (and it's not possible to tell which).

The complication is that I'm building a filtering system that constructs the query dynamically (so it's unknown what tables and columns will be involved, and the conflicts between them).

When I construct the query, I specify the columns in absolute (table.column) format, however the output only ever provides it in column-only format.

Is there any way in which I can force it to return table.column-notation column-names?

This is the (slightly abridged) code I'm using, with ->get() doing the actual retrieving:-

$modelName = Article::class;
$tableName = "articles";
$filters = ["author.name" => "John"];

$selectColumns = ["articles.id", "articles.title", "articles.status", "authors.name", "authors.status"];

// create the query, specifying columns to obtain
$query = $modelName::query();

// build the filters
foreach ($filters as $filter) {
    $query->where($filter[0], $filter[1]);

    // accumulate join if column uses a table other than $tableName
}

$joins = [["authors", "authors.id", "articles.author_id"]];
foreach($joins as $join){
    $query->join($join[0], $join[1], "=", $join[2]);
}

$collection = $query->get();

In the above form, it will retrieve a Collection of Article objects, with the keys id, title, name and status (of authors.status, presumably because internally it's overwriting the preceding articles.status).

I've already tried replacing $modelName::query() with DB::table($tableName), and it doesn't affect the keys being returned (the only difference is the class of the members).

I know I can go all-out and use AS in my selects (e.g. [..."articles.status AS articles_status", "authors.status AS authors_status"... ]), but that feels a little unclean.

Edit: The amount of mis-thinking that went into the above code is quite embarrassing, but since we can't delete questions, I've posted my solution below.

rchaffer

5 years ago

Level 1

As we can't delete questions, I'll leave my solution (as it might help others in future).

Okay, turns out I implemented this in a way that worked in all my test-cases, but that broke under certain conditions (remember to test thoroughly, kids!)

So - forgetting the fact that I omitted any ::select calls in the above code, rendering $selectColumns useless - I now see that I was abusing the Model::query mechanism (which is technically Eloquent, rather than query builder), thereby in-turn abusing Eloquent models (such that they ended up with unrelated attributes set).

Given this, I've now re-worked it all to use the Eloquent querier, specifying relation conditions using ::whereHas, so it now looks like this:-

$modelName = Article::class;
$filters = ["authors.name" => "John"];
$sorts = ["articles.title" => "ASC"];

// start the query
$query = $this->sourceClass::query();

// apply the filters
foreach ($filters as $filter) {
    if(/* filter column belongs to the focal model */) {
        // this is the base model

        $query->where($filter["column"]->name, stripslashes($filter["comparator"]), $filter["value"]);
    } else {
        // this is a related model

        $relation = /* the 1- end of a 1-N relation (i.e. related via *_id) */
            ? Str::singular(Str::snake($modelName))
            : Str::plural(Str::snake($modelName));

        $query->whereHas($relation, function($query) use($filter) {
            $query->where($filter[0], $filter[1]);
        });
    }
}

// apply the sorts
foreach ($sorts as $sort) {
    $query->orderBy($sort[0], $sort[1]);
}

// get the data (this also works with ::paginate)
$this->data = $query->get();

It's now working correctly, plus this ensures that loading of relations is done the correct, Eloquent way!

Since this was my eventual solution, I've posted it here as an answer (as it was my answer to this problem). However, it does not answer the question above (namely: Is there any way in which I can force it to return table.column-notation column-names?). I'm going to hold off marking this as the "best" answer, since an answer to that question would still be useful (and - in my opinion - best). I suspect it will be "no", though.

To sum up: Im dum :S

Please or to participate in this conversation.