Complex Queries in CakePHP 2.x

With CakePHP, a MVC-based PHP framework, it is usually very easy to retrieve data from the database table underlying a specific model, as well as in case the tables are connected with each other through their respective model declarations, as these tables are then joined automatically. However, when one wants to join multiple tables within one query and they are not connected through their models (which occurs quite often), then there nevertheless has to be a way to join the desired tables to get the respective data. The goal is to find a way in CakePHP to accomplish this task.

Taggings:

Submitted by Christian Haslinger on Thu, 12/12/2013 - 18:23
2 answers

Using XHTML and CSS3 and absolute positioning

steps:

  • search the web for text overlay with xhtml and css
  • choose some tutorial among the given search results
  • find out that the css position attribute of the tag is the most important key to lay a text over an image
  • position: absolute - does the trick
  • use "top", "left", "bottom" or "right" to position the text over the image
  • put the image and the element in an containing DIV element
  • set the DIV's CSS width attribute to 100%

    • The CSS

    h1#text{
    position: absolute;
    background: none repeat scroll 0 0 rgba(0, 0, 0, 0.5);
    font-family: "Lucida Sans Unicode","Lucida Grande",Garuda,sans-serif;
    color: #FFFFFF;
    display: inline;
    left: 0;
    top: 75%;
    margin-left: 7px;
    padding: 5px 30px 5px 10px;
    }

    div#container{
    width:100%;
    height:100%;
    }

    The HTML:

    The Web of Tomorrow!


    that's it.

    Submitted by Thomas Ziegelbecker on Thu, 12/12/2013 - 22:58

    Taggings:

    Complex Query Example

    In CakePHP there exist two different ways of achieving the desired goal:

    1)

    $techData = $this->find('all', array(
        'fields' => array('Table1.field_1', 'Table1.field_2', 'Table2.field_3'),
        'alias' => 'Table1'
        'joins' => array(
            array(
                'table' => 'table_2',
                'alias' => 'Table2',
                'type' => 'INNER',
                'conditions' => array(
                    'Table2.field_1 = Table1.field_1'
                )
            )
        ),
        'conditions' => array(
            'field_1' => $cur_id,
            'language_id' => Configure::read('language_id'),
            'active' => true
         ),
        'order' => array('field_1 ASC', 'field_2 ASC'),
        'recursive' => -1
    ));

    Here two tables are joined on a common field (e.g. the ID field), if one wants to add additional tables, these can be added easily by just appending them to the 'joins' array. This approach is the preferred one, since it uses only the native CakePHP way of creating SQL statements and thus benefits from all inbuilt security measures.

    2)

    The second approach would be to use the capability of CakePHP to create queries with raw SQL, however, this approach is discouraged, since it is potentially vulnerable to SQL injection, as the responsibility for handling such a case is transferred from the framework to the user. Still, it shall be shown here. It basically works like this:

    $this->[ModelName]->query("SELECT ... ");

    Submitted by Christian Haslinger on Thu, 12/12/2013 - 18:53

    Taggings: