select only specific fields (mysql) from database using ORM

[GauravK]

I'm trying to find a way to write this SQL line in F3 ORM:

   

select score, count(*) as distribution from  answers where quiz_id_fk='19' group by score order by score asc;

I've tried this, and it kinda works but it returns WAY too much data (it does a "select *" rather than just the 2 fields I want which are score and distribution). This is a waste of resources. 

public function answerDistribution($quiz_id) {
 $this->distribution = 'COUNT(*)';
 return $this->find(['quiz_id_fk = ?', $quiz_id], ['group' => 'score', 'order' => 'score asc']);
 }

How do I select just the two fields I want instead of finding everything?

[ikkez]

use the select method https://fatfreeframework.com/3.6/sql-mapper#select

[GauravK]

Thanks for pointing me in the right directino. I tried this but it's not working (it's returning a full array with all data just like when I use a find() method, rather than just the simple 2-column response that I need)

<?php
class Answers extends DB\SQL\Mapper{
 public function __construct(DB\SQL $db) {
  parent::__construct($db,'answers');
 }


// ... other functions

 public function answerDistribution($quiz_id) {
  return $this->select('score, count(*) as distribution','quiz_id_fk = '. $quiz_id,['group' => 'score', 'order' => 'score asc'] );
 }
}

any idea what I'm doing wrong in the select function?

This is the code in my controller which calls the function:

$distribution = new Answers($this->db);
$this_distribution = $distribution->answerDistribution($id);
$f3->set('distribution',$this_distribution);

When I vardump the variable @distribution in the view, I get a very dense array containing the entire database and not just the simple response that I get when i execute the SQL code in my original post.

[ikkez]

1. why did you changed the virtual field? it was correct like this:

$this->distribution = 'COUNT(*)';

then use this instead:

return $this->select('score',['quiz_id_fk = ?', $quiz_id], ['group' => 'score', 'order' => 'score asc']);

the distribution count field is added automatically to the field list.


2. never var_dump the result of a mapper.. of course it spits out a whole bunch of data, because you're dumping the mapper objects.. just forget about it, you know what fields you have selected, so just use that results in the template

<repeat group="{{@distribution}}" value="{{@reocrd}}">
  <p>{{@record.score}} - {{@record.distribution}}</p>
</repeat>

If you ever need to transform the result into an array.. use the cast method
https://fatfreeframework.com/3.6/sql-mapper#cast

$this_distribution = $distribution->answerDistribution($id);
foreach($this_distribution as $record)
   var_dump($record->cast());

[Gaurav Keerthi]

Thanks. I was following the documentation at  https://fatfreeframework.com/3.6/databases#SeekandYouShallFind which used a virtual field inside the select, so it seemed to be correct. I'll try out your approach too. 

The var dump for @distribution in the view for my original sql query resulted in a simple array. The vardump for the select ORM query seemed unnecessarily heavy (it returned all fields, not just the ones I wanted) and consumed a lot more resources than I thought was necessary. I'll see if this new approach works better. Thanks again! 

--
-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework+unsubscribe@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en
---
You received this message because you are subscribed to a topic in the Google Groups "Fat-Free Framework" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/f3-framework/dmxygGNoi7c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to f3-framework+unsubscribe@googlegroups.com.
To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.

To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/5fcd02de-76fd-495d-920a-e6f0b04b2b23%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.