UNION syntax in Cakephp

by

Too many coders try to limit themselves to the functionality of a framework. DON’T. Use what the framework provides. If it does not have the functionality you seek, then either:

  • Code the functionality you need into a class extension

or

  • Custom spin the code within the framework to suit your needs.

Often, developers try to hammer a square peg into a round hole and wind up doing way too much extra work that really only makes the code complicated. Take a step back and ask why you are using the framework to begin with. It brings structure to an unstructured language. It provides solid reusable foundation to build your application on. It is not intended to be a box to put yourself in and be limited.

UPDATE: I took a minute to read Complex Find Conditions and found your answer:

$joins = array(
    array(
        'table' => 'test_twos',
        'alias' => 'TestTwo',
        'type' => 'LEFT',
        'conditions' => array(
            'TestTwo.id = TestOne.id',
        )
    ),
    array(
        'table' => 'test_threes',
        'alias' => 'TestThree',
        'type' => 'LEFT',
        'conditions' => array(
        'TestThree.id = TestOne.id',
    )
    )
);

$dbo = $this->getDataSource();
$subQuery = $dbo->buildStatement(
    array(
        'fields' => array('*'),
        'table' => $dbo->fullTableName($this),
        'alias' => 'TestOne',
        'limit' => null,
        'offset' => null,
        'joins' => $joins,
        'conditions' => null,
        'order' => null,
        'group' => null
    ),
    $this->TestOne
);
$query = $subQuery;

$query .= ' UNION ';
$joins = array(
    array(
        'table' => 'test_twos',
        'alias' => 'TestTwo',
        'type' => 'LEFT',
        'conditions' => array(
            'TestTwo.id = TestOne.id',
        )
    ),
    array(
        'table' => 'test_threes',
        'alias' => 'TestThree',
        'type' => 'RIGHT',
        'conditions' => array(
        'TestThree.id = TestOne.id',
        )
    )
);

$dbo = $this->getDataSource();
$subQuery = $dbo->buildStatement(
    array(
    'fields' => array('*'),
    'table' => $dbo->fullTableName($this),
    'alias' => 'TestOne',
    'limit' => null,
    'offset' => null,
    'joins' => $joins,
    'conditions' => null,
    'order' => null,
    'group' => null
    ),
    $this->TestOne
);

$query .= $subQuery;

pr($query);

More Related Contents:

  1. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE `id` [closed]
  2. How can create Database using url or other method [duplicate]
  3. Problame with files on SQL
  4. Im trying to create some temporary tables in mysql using php
  5. How can I prevent SQL injection in PHP?
  6. SQL injection that gets around mysql_real_escape_string()
  7. How to include a PHP variable inside a MySQL statement
  8. How to apply bindValue method in LIMIT clause?
  9. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  10. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  11. I cannot get my login form to connect interact properly with mySQL database [closed]
  12. PHP Fatal error: Class ‘PDO’ not found
  13. mysql_num_rows(): supplied argument is not a valid MySQL result resource [duplicate]
  14. Increment value in MySQL update query
  15. SQLSTATE[HY000] [1045] Access denied for user ‘username’@’localhost’ using CakePHP
  16. Do SQL connections opened with PDO in PHP have to be closed
  17. MYSQL Select from tables based on multiple rows
  18. How to execute raw queries with Laravel 5.1?
  19. How to convert ISO8601 to Date format in php
  20. MySQL skipping first row
  21. How to reverse order output of a MySQL query
  22. How do you do multiple SQL statements in one mysql_query?
  23. CakePHP Database connection “Mysql” is missing, or could not be created
  24. MySQL/SQL retrieve first 40 characters of a text field?
  25. Fatal error: Call to a member function query() on null
  26. How would I implement a simple site search with php and mySQL?
  27. Do I have to use mysql_real_escape_string if I bind parameters?
  28. SELECT row by DATEPART()
  29. PDO and MySQL ‘between’
  30. MySQL Join Multiple Rows as Columns

Leave a Comment