Anyway to get the underlying sql statement of a list after a filter applied?

[p38]

Is there anyway to get the underlying sql statement of list when a filte ris applied, or anytime after that?

I would like to use this to run additional queries for reporting?

Paul

 

[rob]

where / when do you need to run the additional queries? In general the listModel has a buildQuery() method which returns the sql used to build the list.

 

[p38]

I will need the sql once a filter has been set. I plan to use this to run additional formatted reports, and add a button event to the list .

will the buildquery() return the sql "post" filter? if so, then this is perfect.

Any examples would be great.

Paul.

 

[rob]

Where abouts though are you going to be doing this? In a form plugin, a calcution etc?

 

[p38]

mostly in a list, idea is to add a custom js button (feature you added a while back and I use a lot) and to fire a report from there.

 

[cheesegrits]

Hmmmm. Well, the buildQuery() is obviously run on the server, not in JS. What would the button actually do? Make an AJAX call to run some totally custom code, presumably in a user_ajax.php file?

-- hugh

 

[rob]

Yeah I think a full description of what you need to do is going to help. To my mind I don't see why you need to get the query on its own. IF you need a report then is that the same as viewing the list but with a specific template, or is there some extra 'thing' that needs to happen which makes this option defunct?

 

[p38]

I actually want to run separate excel and pdf formatted reports generated by external apps/code.

I plan to use fabrik for the user to find his data he wants, as fabrik is well suited to this, but the PDF side of fabrik does not work most of the time for larger lists.

Once the list has been filtered, the user then presses a PRINT button, whereby I use the underlying sql of the list to generate PDF formatted reports externally.

Paul.

 

[rob]

ok so say your button calls a user_ajax page with a url of :

var url = "index.php?option=com_fabrik&format=raw&task=plugin.userAjax&method=customPDF&listid=" + id;

then in components/com_fabrik/user_ajax.php:

class UserAjax
{
  function customPDF() 
  {
     $app = JFactory::getApplication();
     $listid = $app->input->getInt('listid');
     $model = JModel::getInstance('List', 'FabrikFEModel');
     $model->setId($listid);
 
     // This should be the list query containing any applied filters 
     $sql = $model->buildQuery();

    // The list's connection's db
     $db = $model->getDb();
 
     // Do you custom code here.............
  }
}

 

[p38]

thanks I will give this a bash.

Paul