Re: Query and exporting to csv

You might want to ignore some of my recommendations. It turns out that
using a UNION makes a mess of things in Cake. The reason is that PDO
won't return a table name/alias from getColumnMeta() because the
tables in the two selects might be (are likely to be) different. And
that's what Cake relies upon to create its arrays keyed on model
aliases.

I've just spent hours tracking this down because I was trying to use a
UNION also. If I come up with a solution (doubtful) I'll let you know.

On Sat, Jun 30, 2012 at 3:51 PM, lowpass <zijn.digital@gmail.com> wrote:
> First step, confirm that your data is what you expect:
>
> foreach($patient_list as $patient) {
>   die(debug($patient));
>
> if that doesn't work put die(debug($patient_list)) before the foreach.
>
> Second, but maybe unrelated, setting the viewVar $alldrugs will not
> survive a redirect. That is, it wouldn't be available once processing
> has gone on to the view for generatePatientInfo. So it's pointless to
> do the find before checking to see if you should redirect. And
> consider using setAction() instead.
>
> Third, you should put as much logic as you can in the model. Fat
> models/skinny controllers. Create methods in the model to pass the
> data back to the controller.
>
> Fourth, avoid Model::query. It has its uses but those should be rare.
> Instead, create model associations. You can always use the
> 'finderQuery' param in the association array. (Coincidentally, I just
> did the very same thing with a UNION.)
>
> And in doing so, you should emulate how Cake uses model aliases. It
> will make things a lot cleaner in the long run. That goes also for the
> table naming. Instead of drug_pregnancy_links use drugs_pregnancies.
>
> Also, your query might be better using Joins.
>
> For example, in this case you could create a PatientInfo model. In
> your *Drug* model:
>
> public $hasMany => array(
>        'PatientReport' => array(
>                'className' => 'PatientReport',
>                'foreignKey' => false,
>                'finderQuery' => '
> SELECT
>        Patient.id, Patient.first_name, Patient.last_name, Call.id, Call.created
> FROM
>        (patients AS Patient)
> LEFT JOIN
>        calls AS Call
> ON
>        Call.patient_id = Patient.id
> LEFT JOIN
>        lactations AS Lactation
> ON
>        Lactation.call_id = Call.id
> LEFT JOIN
>        drugs_lactations AS DrugLactation
> ON
>        DrugLactation.lactation_id = Lactation.id
> LEFT JOIN
>        drugs AS Drug
> ON
>        DrugLactation.drug_id = Drug.id
> WHERE
>        Drug.id = {$__cakeID__$}
> UNION
> SELECT
>        Patient.id, Patient.first_name, Patient.last_name, Call.id, Call.created
> FROM
>        (patients AS Patient)
> LEFT JOIN
>        calls AS Call
> ON
>        Call.patient_id = Patient.id
> LEFT JOIN
>        pregnancies AS Pregnancy
> ON
>        Pregnancy.call_id = Call.id
> LEFT JOIN
>        drugs_pregnancies AS DrugPregnancy
> ON
>        DrugPregnancy.pregnancy_id = Pregnancy.id
> LEFT JOIN
>        drugs AS Drug
> ON
>        DrugPregnancy.drug_id = Drug.id
> WHERE
>        Drug.id = {$__cakeID__$}'
>        )
> );
>
> If you can't rename the join tables, switch drugs_pregnancies &
> drugs_lactations to your own names.
>
> controller:
>
> public function generateReport($id = null)
> {
>        if (!empty($id))
>        {
>                $this->layout = 'ajax';
>
>                // remove these 2 lines once you know it's working
>                Configure::write('debug', 2);
>                die(debug($this->Drug->fetchPatientReport($id)));
>
>                $this->set(
>                        'data',
>                        $this->Drug->fetchPatientReport($id)
>                );
>        }
> }
>
> Drug.php:
>
> public function fetchPatientReport($id)
> {
>        return $this->find(
>                'first',
>                array(
>                        'fields' => array(
>                                $this->alias.'.id',
>                                $this->alias.'.generic'
>                        ),
>                        'conditions' => array(
>                                $this->alias.'.id' => $id
>                        ),
>                        'contain' => array(
>                                'PatientReport'
>                        )
>                )
>        );
> }
>
> On Fri, Jun 29, 2012 at 10:48 AM, Shosho <shinsuio0012@gmail.com> wrote:
>> I'm trying to export certain information to a CSV file. I have a drop down
>> list to select the drug you want to generate the information for and it
>> sends the id to the function to generate the csv(That is sending the correct
>> ID selected). The query runs and gets the information I want, I use it in
>> another view, but i'm having issues getting the data to get put into the
>> csv. I'm not sure what else to try. It doesn't put anything into the csv
>> despite accessing it the same way I do in the view. Any help is appreciated.
>>
>> Functions:
>>  function report(){
>>    $this->set('alldrugs', $this->Drug->find('list', array(
>>      'cache' => 'drugList',
>>      'cacheConfig' => 'sql',
>>      'fields' => array('id', 'generic'),
>>      'order' => 'Drug.generic',
>>      'recursive' => -1,
>>    )));
>>    if (!empty($this->data['DrugCalls'])) {
>>
>> $this->redirect(array('controller'=>'drugs','action'=>'generatePatientInfo',$this->data['DrugCalls']['DrugList']['id']));
>>      }
>>  }
>>  function generatePatientInfo($id=null) {
>>    $this->layout = 'ajax';
>>    // get drug info
>>    $drug = $this->Drug->read(
>>      array(
>>        'Drug.id', 'Drug.generic'
>>      ),
>>      $id
>>    );
>>    $this->set('drug',$drug);
>>    $patients = $this->Drug->query('SELECT patients.id, calls.id call_id,
>> patients.first_name, patients.last_name, calls.created '
>>      . 'FROM drugs, drug_lactation_links, lactation, calls, patients '
>>      . 'WHERE drugs.id = ' . $id . ' AND drugs.id =
>> drug_lactation_links.drug_id AND '
>>      . 'drug_lactation_links.lactation_id = lactation.id AND
>> lactation.call_id = calls.id AND calls.patient_id = patients.id '
>>      . 'UNION '
>>      . 'SELECT patients.id, calls.id call_id, patients.first_name,
>> patients.last_name, calls.created '
>>      . 'FROM drugs, drug_pregnancy_links, pregnancies, calls, patients '
>>      . 'WHERE drugs.id = ' . $id . ' AND drugs.id =
>> drug_pregnancy_links.drug_id AND '
>>      . 'drug_pregnancy_links.pregnancy_id = pregnancies.id AND
>> pregnancies.call_id = calls.id AND calls.patient_id = patients.id '
>>      . 'ORDER BY created DESC'
>>    );
>>    $this->set(compact('patients'));
>>  }
>>
>>
>> View:
>> <?php
>> // add the header row
>> $header = array('First Name','Last Name');
>>
>> $csv->addRow(array('Enter Generic: ' . $drug['Drug']['generic']));
>> $csv->addRow($header);
>>
>> // add all the data
>> foreach($patient_list as $patient) {
>>  $csv->addRow(array(
>>    $patient[0]['first_name'],
>>    $patient[0]['last_name'],
>>  ));
>> }
>> $csv->addRow(array());
>>
>> // render the CSV file
>> echo $csv->render('Patients-list.csv');
>> ?>
>>
>>
>> --
>> View this message in context: http://cakephp.1045679.n5.nabble.com/Query-and-exporting-to-csv-tp5709159.html
>> Sent from the CakePHP mailing list archive at Nabble.com.
>>
>> --
>> Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
>> Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
>>
>>
>> To unsubscribe from this group, send email to
>> cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php