CODEWITHSUNDEEP
mysqlsqljoinpropel
Lorenzo Polidori
Lorenzo Polidori

Reputation: 10512

Single SQL query to retrieve information and count from 3 tables with Propel 1.6

I have the following (simplified) database structure:

table book

id
title

table page

id
bookId
number

table figure

id
pageId

page.bookId and figure.pageId are foreign keys pointing to the primary keys book.id and page.id respectively.

A book can have no pages and a page can have no figures.

For a specified book id, with a single sql query, I'd like to retrieve

  1. the book information
  2. a list of pages with their respective information
  3. the number of figures for each page

An example JSON representation of the result is as follows:

{
    "id": 34, 
    "title": "The title"
    "pages": [
    {
        "id": "44",
        "number": 1
        "figureCount": 2
    },
    {
        "id": "45",
        "number": 2
        "figureCount": 5
    },
    ...
    ]
}

Is that possible with Propel 1.6 and what should be the resulting SQL query?

I use MySql 5.0.

EDIT: this is my (simplified) Propel schema.xml:

<table name="book" phpName="Book">
    <column name="id" type="bigint" required="true" primaryKey="true" autoIncrement="true" />
    <column name="title" type="varchar" size="512" required="true" />
</table>

<table name="page" phpName="Page" >
    <column name="id" type="bigint" required="true" primaryKey="true" autoIncrement="true" />
    <column name="book_id" type="bigint" required="true" />
    <column name="number" type="integer" required="true" />
    <foreign-key foreignTable="book" phpName="Book" refPhpName="Page" onUpdate = "cascade" onDelete = "cascade">
       <reference local="book_id" foreign="id" />
    </foreign-key>
</table>

<table name="figure" phpName="Figure">
    <column name="id" type="bigint" required="true" primaryKey="true" autoIncrement="true" />
    <column name="page_id" type="bigint" required="true" />
    <foreign-key foreignTable="page" phpName="Page" refPhpName="Figure" onUpdate = "cascade" onDelete = "cascade">
       <reference local="page_id" foreign="id"/>
    </foreign-key>
</table>

Upvotes: 3

Views: 283

Answers (2)

sgeddes
sgeddes

Reputation: 62861

I haven't used that ORM, but can't imagine it wouldn't support what you're trying to do. Is this the SQL you're looking for:

SELECT 
  B.Id,
  B.Title,
  P.Id as PageId,
  P.Number,
  COUNT(F.Id) as FigureCount
FROM 
  Book B
    LEFT JOIN Page P ON B.Id = P.BookId
    LEFT JOIN Figure F ON P.Id = F.PageId
GROUP BY 
  B.Id,
  B.Title,
  P.Id,
  P.Number

And here is the SQL Fiddle.

Note: I don't think you intended for both page objects to have the same id (44) -- I changed one of those to 45.

Upvotes: 3

j0k
j0k

Reputation: 22756

According to the great answer from @user1073631, the propel query should be something like this:

$books = BookQuery::create()
  ->select(array('Book.Id', 'Book.Title', 'Page.Id', 'Page.Number'))
  ->withColumn('count(Figure.ID)', 'FigureCount')
  ->leftJoin('Book.Page')
  ->leftJoin('Page.Figure')
  ->groupBy('Book.Id')
  ->groupBy('Book.Title')
  ->groupBy('Page.Id')
  ->groupBy('Page.Number')
  ->find();

I'm not sure about the join() it could be better to see your generated classes to be sure about that point.

Upvotes: 5

Related Questions