Hello,

I am working on a table that shows some data and stats on what has been created on the site, based off a date range.

There's "contacts.php", where it stores clients.
And there's "contrats.php", where we create contracts for specific events. Each entries in "contrats.php" has a relation field related to the contacts, since we must asign a contact to each contracts.

So I came up with this for the search form :

Code: Select all

<cms:form name="searchtop"anchor='0'>
    <cms:if k_success>
        <cms:set my_start_on=frm_start_on  scope='global'/>
        <cms:set my_stop_before=frm_stop_before  scope='global'/>
    </cms:if>

    <div>
        <label>Start Date</label>
        <cms:input type="text" name="start_on" id="start_on" />
    </div>
    <div>
        <label>Before Date</label>
        <cms:input type="text" name="stop_before" id="stop_before" />
    </div>

    <div class="buttonset">
        <cms:input type="submit" class="fbsubmitbtn" value="Filtrer" name="submit"/>
    </div>
</cms:form>

And this part for the table showing the data on submit :

Code: Select all

<table class="table table-primary table-list">
        <thead>
          <tr>
            <th class="col-my_text no-hide">Client</th>
            <th class="col-my_text no-hide">Personne ressource</th>
            <th class="col-my_text no-hide">Télephone</th>
            <th class="col-my_text no-hide">Courriel</th>
            <th class="col-my_text no-hide">Contrats vendus</th>
          </tr>
        </thead>
        <tbody id="listing">

          <cms:pages masterpage="contacts.php" id=filtered_ids aggregate_by='contrats.php::contact_dropdown' orderby="k_rel_count" custom_field="type=Client" limit="50" paginate='1' show_future_entries='1'>
  
            <cms:if k_paginated_top >
              <cms:set my_records_found='1' scope='global'/>
              <cms:if k_paginator_required >
                 Page <cms:show k_current_page /> of <cms:show k_total_pages /><br />
              </cms:if>
              <cms:show k_total_records />
             </cms:if>

            <tr>
              <td class="col-my_text no-hide"><a href="<cms:admin_link/>" target="_blank"><cms:show co_nom/></a></td>
              <td class="col-my_text no-hide"><cms:show personne_ressource/></td>
              <td class="col-my_text no-hide"><cms:show personne_telephone/></td>
              <td class="col-my_text no-hide"><a href="mailto:<cms:show personne_email/>"><cms:show personne_email/></a></td>
              <td class="col-my_text no-hide">
                <cms:pages masterpage="contrats.php" count_only='1' orderby='' custom_field="contact_dropdown=<cms:show k_page_name/>" start_on=my_start_on stop_before=my_stop_before>
                  <cms:show k_archive_count />
                </cms:pages>
              </td>
            </tr>
            <cms:paginator />
          </cms:pages>
        </tbody>
      </table>

I want to order the entries by the number of contracts. So technically, I want to be able to search "which contacts got the most contracts within a specific time frame".

It works fine when the page actually loads the first time, without using the search engine.
The contact with the most contracts in relation to him is in first place, and so on.

But if I enter a date in the "start" and "end" date, click submit, it will keep the same order, but only update the number of contracts at the end of the row. So even if the contact has 1 contract instead of being the top one at 45 before clicking submit, he remains in the first place. Any ideas why?

[trendoman]

Any ideas why?

Aggregation is applied without constraints of time.

Code: Select all

start_on=my_start_on stop_before=my_stop_before

This filtering only happens in the second request which shows actual filtered number of contracts.

With posted coding, it is no wonder if none of the contracts fit within the specified timeframe but nevertheless appear in one of the contact rows with "0" contracts.

Make sense.
I tried adding the "start_on=my_start_on stop_before=my_stop_before" parameters to the first query, but it doesn't change anything.

The issue is the order it is displayed.

Is there any ways to ask the search engine to re-check "within that time frame, which contact has the most contracts"?
I don't want it to display "contacts" based off their published_date, that's why I thought I shouldn't use the "start_on" and "stop_before" parameters for the first query.

With the current code, it doesn't always show "0 contracts". It actually filters it correctly and display the right amount of contracts within that time frame, it just doesn't re-order the list to show the "contact with most contracts" order when I click "search".

Thanks a lot!

[trendoman]

I can see that aggregation is used in the reverse form, it is interesting to experiment with direct form instead - always list contracts (aggregated by contacts) in the first query and find relevant contact within it.

Quickest solution though is a custom SQL query. Shouldn't be a problem for you to add the date filter to the mixture by employing help from return_sql='1' of cms:pages.