Custom pagination on different models with symfony and Doctrine

As I googled and asked, and nobody could give me a better solution, I post it, conviced that the following code is pure crap.

The problem©

I have a cool schema, which uses both column aggregation and concrete table inheritance, and a simple search engine to develop, which needs to search the field title ( or name ) of every entity, regardless its definition.

This means, for example, that it has to look for the query ( Es. “John” ) in the sfGuardUser model, matching the name column, and also in the Post model, matching the title column.

Worthless to say, I got something like 15 tables: not that much, neither a couple.

Solution: use Lucene

Yes, I know: Lucene ( or Solr ) would be the best way to do that; but I can’t use it as I forced myself not to use Lucene every time I need a search engine that simple. I need to search for a single word, in 15 tables, between something like 400 records.

Lucene would swear at me if I use it for this kinda purposes.

Solution2: be a column-aggregation-holic

Another time: I can’t.

The whole schema has different and various fields so I cannot stand a full-of-null record set.

Solution3: UNIONs

Here’s how I “solved” it, with UNIONs and a custom pagination.

Alex, CTO @ DNSEE, said the drawbacks of this approach are justified by the poverty of the SE.

Breaking the concept of fat models and thin controllers, I fulfilled my controller with the following code…

First step: build the UNION

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php

$WHERE = "WHERE (title LIKE '%{$this->word}%')";

$query = "( 
  SELECT id, title, intro, type, date FROM post $WHERE) 
UNION ( 
  SELECT id, title, description, type, date FROM boats $WHERE) 
UNION ( 
  SELECT id, title, intro, 'harbour', date FROM harbour $WHERE) 
...
...
...
ORDER BY date DESC ";

Execute the query

1
2
3
<?php

$this->items = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);

Prepare the paginator

1
2
3
4
5
6
<?php

$count        = count($this->items);
$page         = ( $request->getParameter('page') - 1 ) . 0;
$this->items  = array_slice($this->items, $page, $limit);
$this->pages  = ceil($count / $limit);

In the view you will only need to render $items and print the pagination:

1
2
3
4
5
<?php

for($x = 0; $x < $pages; $++)
  ...
  ...

Hi there! I recently wrote an ebook on web application security, currently sold on leanpub, the Amazon Kindle store and gumroad.

It contains 160+ pages of content dedicated to securing web applications and improving your security awareness when building web apps, with chapters ranging from explaining how to secure HTTP cookies with the right flags to understanding why it is important to consider joining a bug bounty program.

Feel free to skim through some of the free chapters published on this blog and, if the content seems interesting enough to you, grab a copy on leanpub, the Amazon Kindle store, gumroad or simply checkout right down below!

Buy the Web Application Security ebook for $6.99

In the mood for some more reading?

...or check the archives.