Need to trigger a server action based on a filtered list

[lcollong]

Hi,

First the whole scope. We have developed a Fabrik app "sticked" on Acymailing. Thus we have Fabrik's lists for subscribers, lists etc... the subscriber list is joined 1-1 to a specific list allowing us to enhance the Acymailing's possibilities. One among other : the possibility to associate a subscriber to several (repeated) categories (DBJ & CDD). The idea is to let the administrators use the power of Fabrik to filter their subscriber list (using basic filtering, full word one and advanced one). In particular, using the category to select, say, "all women between 30 and 40, living in Paris belonging to category 'manager' and to the category 'graduate' ". upon they are happy with their filtering they hit a button which will subscribe all these records to an acymailing list (in the purpose to send them a newsletter). Actually, this button will fill the acymailing table which is doing the "n-m" relationship between subscribers and lists.

It's very comfortable to use the php list plugin to make this happen. However, if you want to catch all the rows in the same "click", you firstly have to change the display limit to "all" in order to be able to check all the box and trigger the php script for all rows in one move. The problem raise when the selection made is very large (the db is over 130 000 records, one selection could involve several thousands records).

Is there any way to catch the whole list of filtered pk values allowing me to trigger the right sql whatever rows are actually checked ? Another way would be to fetch back the sql query used by Fabrik to actually render the result ? Just the way fabrikDebug shows it. I'll have to change the "limit" to fetch all the data....

I've search a way using $model->setLimits(0,-1); $model->getData(); but I always get the displayed data, not all the rows concerned by the filter.

I'm afraid the js or php list plugin are fired after the date being loaded, thus making impossible to fetch all the rows. I don't see how to do it using php_events plugin as I need the action being under user control.

I've the feeling something should be doable here but I would appreciate any starting point idea.

 

[cheesegrits]

Hmmm, interesting.

I think you should be able to do it, but you'd have to re-run the main getData query, to remove the limits.

Something like this ...

$myDb = $model->getDbo();
$myQuery = $model->buildQuery();
$myDb->setQuery($myQuery);
$myRows = $myDb->loadObjectList();

... should get you a standard object with all the fields (raw and non-raw) we would usually get to build the list data from, without pagination.

Or you could just grab the filter SQL, which should be in $model->_whereSQL['queryX'][1], where X is your list ID, which will be the WHERE clause for the main query. Or it might be ['1'] rather than [1], I can't recall if that's numeric or a string. Then just use that where clause on your own much simpler query, which just grabs the id's.

IIRC, the $model->_whereSQL array should be there without you having to run the buildQuery(), as we'll already have run that prior to calling your plugin code, so it's all cached in the model.

One potential issue with just using the _whereSQL is if you have much by way of joins, and you filter on those, the join aliases (where we add _X to the table names to avoid clashes) may not be obvious. However, you could generate our join clause by calling $model->buildQueryJoin(), and add that as well as the filter SQL to your own query.

-- hugh

 

[lcollong]

Thanks ! I feel now close of the goal. Here is the poor php snipset I've used to test the concept. It seems to work exactly the way I expected :

$where = $model->_whereSQL[query1][1];
echo "where : <pre>";print_r($where);echo"</pre>";
$joins = $model->buildQueryJoin();
echo "joins : <pre>";print_r($joins);echo"</pre>";
$db = JFactory::getDBO();
$db->setQuery("SELECT count(DISTINCT `f_annuaire`.`fk_subid`) FROM `f_annuaire` " . $joins . " WHERE " . $where);
$result = $db->loadResult();
if($db->getErrorMsg()) die("Erreur : ".$db->getErrorMsg());
echo "result :" . $result;
exit;

The first array index "query1" does not relate to the listid. Is there any possibility to have a query2 ?... for now, I've tested it with some pretty complex filter with right results. I'll check with advanced filtering options and tell you.

 

[cheesegrits]

Oh right, yeah, the 1 in query1 refers to the $incFilters setting. So query1 is with filters, query0 is without. So you'll always want query1.

BTW, you should definitely put ['query1'] in quotes, otherwise it'll be tossing a PHP notice about "assumed string constant' or some such.

Cool, glad that works for you.

-- hugh

 

[lcollong]

Some news in case it might help some one else.

I've moved the code inside the template and set session variables this way (begining of default.php) :

//_______________
// fetch the model
//
$model = $this->getModel();
//
// fetch the where part of the filter
$where = $model->_whereSQL['query1'][1];
 
// no action if list is not filtered
if (strlen($where) > 0) {
 
    // fetch the join part of the query
    $joins = $model->buildQueryJoin();
    $bouton_abonner = '<a href="index.php?option=com_fabrik&view=form&formid=9">Abonner ces utilisateurs</a>';
    $session = JFactory::getSession();
    $session-> set('clause_where',$where);
    $session-> set('clause_joins',$joins); 
} else {
    $bouton_abonner = '<a href="#top" class="disabled">Filtrer la liste</br>pour pouvoir abonner</a>';
    $session = JFactory::getSession();
    $session-> set('clause_where',"");
    $session-> set('clause_joins',""); 
}
//_________________________

It set up a new button link if the list is filtered and pass the where/join data to the session variables (or clear them).

Then I create a new button that will redirect to a fabrik form (id=9) if the list is filtered :

<div class="fabrikButtonsContainer row-fluid">
    <ul class="nav nav-pills pull-right">
        <li>
            <?php echo $bouton_abonner; ?>
        </li>
    </ul>
</div>
</form>

The fabrik form #9 has no related table (nothing to store). There is only one DBjoin element pointing to the acymailing table containing the list (#__acymailing_list) and 2 pieces of code (php plugin).

To remind on top of the form the number of users that will be suscribed to the selected list :

$session = JFactory::getSession();
$where = $session->get('clause_where');
$joins = $session->get('clause_joins'); 
 
if (strlen($where) > 0) {
  $db = JFactory::getDBO();
  $db->setQuery("SELECT count(DISTINCT `f_annuaire`.`fk_subid`) FROM `f_annuaire` " . $joins . " WHERE " . $where);
  $result = $db->loadResult();
  if($db->getErrorMsg()) die("Erreur : ".$db->getErrorMsg());
} else {
  $result = 0;
}
  $session-> set('total_abonnes',$result);
 
if (intval($result) > 0) {
  return "Vous allez abonner " . $result . " utilisateurs.";
} else {
  return '<p style="color: red;"><strong>Attention ! Aucun utilisateur s?lectionn?.</strong></p>';
}

It reads back the session variable, use them to query the DB and verify the number of records which will be concerned and display it or a warning message in case the result is zero. Also it set a new session variable with the value.

Finally if the user hit the submit key (on after process) :

$session = JFactory::getSession();
$where = $session->get('clause_where');
$joins = $session->get('clause_joins');
$total_abonnes = intval($session->get('total_abonnes'));
 
$acy_listid = (int)'{___acy_list_raw}';
if ($acy_listid > 0) {
 
  $acy_subdate=time();
  $db = JFactory::getDBO();
  $db->setQuery("SELECT count(id) FROM #__acymailing_listsub");
  $oldid = (int)$db->loadResult();
//  var_dump($oldid);
  $db->setQuery("INSERT IGNORE INTO #__acymailing_listsub (listid,subid,subdate,unsubdate,status) SELECT " . $acy_listid . ", `f_annuaire`.`fk_subid`, " .$acy_subdate . ", 0, 1 FROM `f_annuaire` " . $joins . " WHERE " . $where);
  $result = $db->execute();
 
  if($db->getErrorMsg()) die("Erreur : ".$db->getErrorMsg());
  $db->setQuery("SELECT count(id) FROM #__acymailing_listsub");
  $newid = (int)$db->loadResult();
//  var_dump($newid);
  if ($newid == $oldid) {
    JFactory::getApplication()->enqueueMessage("Aucun utilisateur n'a ?t? abonn?. Il est possible qu'ils le soient tous d?j?" , 'notice');
  } elseif (($newid - $oldid) == $total_abonnes) {
    JFactory::getApplication()->enqueueMessage("Les " . $total_abonnes . " utilisateurs s?lectionn?s ont tous ?t? abonn?s.", 'message');
  } else {
    JFactory::getApplication()->enqueueMessage("Seuls " . ($newid - $oldid) . " utilisateurs sur les " . $total_abonnes . " s?lectionn?s ont ?t? abonn?s. La diff?rence provient probablement d'utilisateurs qui ?taient d?j? abonn?s ? cette liste.", 'message');
  }
} else {
  JFactory::getApplication()->enqueueMessage("Op?ration annul?e : vous n'avez pas indiqu? de liste" , 'warning');
}
$session-> set('abonnements',"");
$session-> set('clause_where',"");
$session-> set('clause_joins',"");
$session-> set('total_abonnes',"");

It reads back the session variables (may be not necessary ?), verify how many records are there in the list_sub table before inserting the new ones. The SQL will subscribe new user to the selected list ($acy_listid) except if he has already subscribe (IGNORE). The placeholder ({___acy_list_raw}) is the dropdown allowing the user to select the right list.
A new count of records allow to setup different messages whether all users have been subscribed or only part of them or even none at all.

After the submit, the original filtered list is displayed back together with the message saying how things went.

It has to be completed (DB error branches) but it seems to work pretty well.

Thanks for your help !

Laurent

 

[cheesegrits]

Yay! Glad you got that sorted, and thanks for posting your code.

-- hugh