Re: Query and exporting to csv

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