By ParisLiakos on

this is the full query

    SELECT t.* 
    FROM
      table AS t
    JOIN
    ( SELECT title, MAX(last_sent) AS maxts
      FROM table
      GROUP BY title
    ) AS grp
    ON grp.title = t.title
    AND grp.maxts = t.last_sent
    LEFT OUTER JOIN table_l list ON t.title = list.title

I try to convert it to db_select since i need to use pager and order by on my table.

i want to do something like this:

    $query = db_select('table', 't')
      ->extend('PagerDefault')
      ->extend('TableSort');
    $query->fields('t');
    $query->join('
      SELECT title, MAX(last_sent) AS maxts
      FROM table
      GROUP BY title',
      'grp', 'grp.title = t.title AND grp.maxts = t.last_sent');
    $query->leftjoin('table_l', 'list', 't.title = list.title');
    $query->limit(50);
    $query->orderByHeader($header);
    $result = $query->execute();

but this of course doesn't work since join needs a table name as first argument..
does db_select even support something like this,or do i need to do it manually?

⋅ Categories: Drupal 7.x, Drupal 8.x

Comments

ParisLiakos’s picture

ParisLiakos commented

i ended up using db_query, since db_select doesn't support that complex queries..
i lost paging and sorting abilities though :(

would be glad if someone had a solution

neilotoole’s picture

neilotoole commented

I have a lot of quite complex queries (with subqueries etc) that I just couldn't seem to get the query builder to output the correct SQL for. And I needed paging. So, I reimplemented pager_query() for D7. Only lightly tested, but it seems to work.

/**
 * Perform a paged database query.
 *
 * Use this function when doing select queries you wish to be able to page. The
 * pager uses LIMIT-based queries to fetch only the records required to render a
 * certain page. However, it has to learn the total number of records returned
 * by the query to compute the number of pages (the number of records / records
 * per page). This is done by inserting "COUNT(*)" in the original query. For
 * example, the query "SELECT nid, type FROM node WHERE status = '1' ORDER BY
 * sticky DESC, created DESC" would be rewritten to read "SELECT COUNT(*) FROM
 * node WHERE status = '1' ORDER BY sticky DESC, created DESC". Rewriting the
 * query is accomplished using a regular expression.
 *
 * Unfortunately, the rewrite rule does not always work as intended for queries
 * that already have a "COUNT(*)" or a "GROUP BY" clause, and possibly for
 * other complex queries. In those cases, you can optionally pass a query that
 * will be used to count the records.
 *
 * For example, if you want to page the query "SELECT COUNT(*), TYPE FROM node
 * GROUP BY TYPE", pager_query() would invoke the incorrect query "SELECT
 * COUNT(*) FROM node GROUP BY TYPE". So instead, you should pass "SELECT
 * COUNT(DISTINCT(TYPE)) FROM node" as the optional $count_query parameter.
 *
 * @param $query
 *   The SQL query that needs paging.
 * @param $limit
 *   The number of query results to display per page.
 * @param $element
 *   An optional integer to distinguish between multiple pagers on one page.
 * @param $count_query
 *   An SQL query used to count matching records.
 * @param $args
 *   An array of values to substitute into the query. If the query uses named
 *   placeholders, this is an associative array in any order. If the query uses
 *   unnamed placeholders (?), this is an indexed array and the order must match
 *   the order of placeholders in the query string.
 * @param $options
 *   An array of options to control how the query operates.
 *
 * @return DatabaseStatementInterface
 *   A prepared statement object, already executed, or FALSE if an early failure occurred.
 *
 * @see DatabaseConnection::defaultOptions()
 *
 * @ingroup database
 */
function pager_query($query, $limit = 10, $element = 0, $count_query = NULL, array $args = array(), array $options = array()) {

  if(empty($count_query)) {
    // $count_query is not set, we'll try to build one
    
    // Check if $query is a new-fangled statement object
    if(is_object($query)) {
      $count_query = $query->countQuery();
    }
    elseif(is_string($query)) { // Nope, $query is a string
      $count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'), array('SELECT COUNT(*) FROM ', ''), $query);
    }  
  }
  
  if(empty($count_query)) {
    // Exit early if we can't build the count query
    return FALSE;
  }
  
  if(is_object($count_query)) {
    // If it's a statement object, we call execute()
    $count_result = $count_query->execute($args);
  }
  else {
    // If it's just a string, we call db_query().
    $count_result = db_query($count_query, $args);
  }
  
  if($count_result === FALSE) {
    // Exit early if the count query failed
    return FALSE;
  }
  
  $total_items = $count_result->fetchField();
  $current_page = pager_default_initialize($total_items, $limit, $element);
  $offset = $limit * $current_page;

  return db_query_range($query, $offset, $limit, $args, $options);
}
Bagz’s picture

Bagz commented

I am pretty sure I have done this in the past, and from memory you have to create another query object.
Can't find my code right now so I am working from memory here, and I'm pretty sure I have seen an example somewhere.....

I believe it works someting like this:

$innerquery = db_select('table', 't')
etc. etc.

    $query = db_select('table', 't')
      ->extend('PagerDefault')
      ->extend('TableSort');
    $query->fields('t');
    $query->join($innerquery);
    $query->leftjoin('table_l', 'list', 't.title = list.title');
    $query->limit(50);
    $query->orderByHeader($header);
    $result = $query->execute();
Bagz’s picture

Bagz commented

I was on the right track. If you look at a file called database_test.test in your Drupal installation (part of the simpletest module), you will find this:

    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery->addField('tt', 'pid', 'pid');
    $subquery->condition('priority', 1);

    // Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select('test', 't');
    $select->join($subquery, 'tt', 't.id=tt.pid');
    $select->addField('t', 'name');

    // The resulting query should be equivalent to:
    // SELECT t.name
    // FROM test t
    //   INNER JOIN (SELECT tt.pid AS pid FROM test_task tt WHERE priority=1) tt ON t.id=tt.pid

Also noticed in the documentation somewhere that even on the db_select you do not need to specify a table, you can specify another query object.

Hope this helps. Things like this are extremely difficult to unearth...

ParisLiakos’s picture

ParisLiakos commented

thank you!!!
that indeed works:)))

btw i have send you an email through drupal's contact form about your module.did you ever get it?