How to use fields instead of a *

[marcelloh]

I am trying to select just some fields to reduce the data I have to put into my json result. I looked into building the query myself:

$sql = "SELECT client.field1, client.field2, client.field3 FROM client where city='Amsterdam';
$query = $this->query($sql);
$result = $query($sql)
    ->with(['department', 'country'])
    ->order(['name' => 'ASC']);

But this gives me an error: Call to undefined method Spot\Entity\Collection::with()

and I can't seem to find in the docs, or in the code, how this should be done.

I posted this by accident in the spot1 issues, but now in spot2. Vance already mentioned, that if I don't use the querybuilder, with doesn't work. So the final question would be: Can I get only fields instead of a asterix AND use the query builder?

In my workaround, I just remove all unnecessary data from the result, but that takes time, which seems like a waist to me.

[marcelloh]

My workaround btw: // sqlFields : comma separated list of allowed fields

    $allowed  = explode(',', $sqlFields);
    $return = [];
    foreach ($result as $row) {
        $return[] = array_intersect_key($row->data(), array_flip($allowed));
    }

[marcelloh]

I also tried with: $result = $this->select($allowed) ->where($aFilter) But instead of the 4 fields I expect, I get all the fields back, and wen I debug the query it does (according to the logger), it only queries the 4 fields I did put in. Looks like an X-file to me ;-)

[FlipEverything]

From the documentation:

Spot will load ALL returned columns on the target entity from the query you run. So if you perform a JOIN or get more data than the target entity normally has, it will just be loaded on the target entity, and no attempt will be made to map the data to other entities or to filter it based on only the defined fields.

I don't think that reducing the data by adding select constraints is a good idea If you use spot. I would filter the json created from the query.

Personally I have a wrapper around spot which manages the returned json data set based on session and user permissions.

[marcelloh]

But why does an option like: $result = $this->select($allowed) exists, because it still returns the same as: $result = $this->select()

[FlipEverything]

Spot is created with the motivation to work with entities. You need all of the fields for a valid, consistent entity.

Don't quote me on that but I think the $fields parameter exists for the SELECT COUNT(*) function.

[marcelloh]

If I debug, I can follow the program all the way to: read(Query $query) where it is performing the right sql (so just with the fields I want), in $stmt, but somehow what is inside $collection is much more. Will investigate further tomorrow

[tuupola]

What is the problem you are trying to solve by querying only some of the fields?

[marcelloh]

The problem is, that the query gives me too much row information , which at first has to transported into memory, and then has to be filtered out by me again. That is memory consuming and time consuming. And since the ->select() works with fields, I expect it somehow to work with the given fields. In that case, it will only resolve the fields I want, and there is less memory and not a routine afterwards to peel off the result-set. in normal sql this works, so it somehow should work here too. In Doctrine this is possible too, so it is not a weird thing to ask.

[marcelloh]

Ok. I've puzzled a bit more, and changed a few parts of the ORM software. Now it works :-) My query resolved 3535 records The old query with the removal of the redundent data took: 5017msec and now with the improvement: 1873 msec

[vlucas]

I would be curious to know which parts of the code you changed, and exactly which issues you were running into.

[marcelloh]

Do you mean the speed and member ry related issue? Or the issues while fixing it? Op 13 apr. 2016 16:16 schreef "Vance Lucas" notifications@github.com:

I would be curious to know which parts of the code you changed, and exactly which issues you were running into.

— You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub https://github.com/vlucas/spot2/issues/153#issuecomment-209467268

[FlipEverything]

@marcelloh @vlucas I can't say anything about this issue. If I access one of the entities with all of the relations, the whole query runs only for ~68 ms.

[marcelloh]

yes, but imagine (like in my case) select * from enormous_table_with lots_of fields against: select field1,field2,field3 from enormous_table_with lots_of fields (that last one is much faster to load and to process) This is what this issue was about