Is it possible in PDO MySQL to LIMIT by number of GROUPS, not total rows?

Question

As you can see this query is returning 5 rows, but only 3 groups. Is there a way to get this to return 5 groups with an unlimited number of rows in each group?

Many thanks in advance!

The Query:

  $query = 'SELECT t.tenderKey, t.tenderId, t.clientId, t.shortDescription, t.status, t.created
            FROM '.$this->tableTenders.' as t
            INNER JOIN '.$this->tableClients.' as c on t.clientId = c.clientId
            INNER JOIN '.$this->tableUsers.' as cb on t.createdBy = cb.officeId
            LEFT JOIN '.$this->tableUsers.' as lm on t.lastModified = lm.officeId
            ORDER BY t.created DESC LIMIT :offset, :perpg';
  $stmt = $this->conn->prepare($query);
  $stmt->bindValue(':perpg',    5,     PDO::PARAM_INT);
  $stmt->bindValue(':offset',   0,     PDO::PARAM_INT);
  $stmt->execute();
  $data = $stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

The Data:

"cfe0e072-f969-71f6-1578-faa0964904dd": [
  {
    "tenderId": "8064",
    "clientId": "1000",
    "shortDescription": "",
    "status": "draft"
  }, {
    "tenderId": "8063",
    "clientId": "1000",
    "shortDescription": "",
    "status": "draft"
  }, {
    "tenderId": "8062",
    "clientId": "1000",
    "shortDescription": "",
    "status": "draft"
  }
],
"642939b3-0de2-9c8a-aeed-bf33856b08eb": [{
  "tenderId": "8061",
  "clientId": "1000",
  "shortDescription": "",
  "status": "draft"
}],
"b01cb9b3-8794-6410-e1b6-014a5c1b0a7f": [{
  "tenderId": "8060",
  "clientId": "1000",
  "shortDescription": "",
  "status": "draft"
}]

PDO doesn't run your queries. It returns just what your **SQL** found. And no, you cannot limit the number of groups in SQL. You can try to find the desired groups first and then add them as a condition to your query — Your Common Sense, Oct 28 '20 at 05:32

Ged Clack · Answer 1 · 2020-11-25T05:39:35.790

-1

Your query should be more/less like this:

SELECT t.tenderKey, t.tenderId, t.clientId, t.shortDescription, t.status, t.created, cb,officeId, lmx.officeId AS 'LastModified_officeId'
    (SELECT lm.officeId 
     FROM '.$this->tableUsers.' as lm 
     WHERE t.lastModified = lm.officeId LIMIT 1) lmx
FROM '.$this->tableTenders.' as t
JOIN '.$this->tableClients.' as c on t.clientId = c.clientId
JOIN '.$this->tableUsers.' as cb on t.createdBy = cb.officeId

edited Nov 25 '20 at 05:39

answered Oct 28 '20 at 03:49

Ged Clack

1
2

I've had no luck with GROUP BY, PDO seems to just ignore it and return everything. – M_Becker Oct 28 '20 at 04:31
Can you elaborate more about what are you trying to do here with some sample data? – Ged Clack Nov 25 '20 at 05:42

Is it possible in PDO MySQL to LIMIT by number of GROUPS, not total rows?

1 Answers1