CODEWITHSUNDEEP
phpsqljoinbuildersqlbuilder
David
David

Reputation: 1031

SQL Builder for PHP, with JOIN support?

Are any of you aware of a library that helps you build/manipulate SQL queries, that supports JOIN's?

It would give a lot of flexibility i'd think if you have something where you could return an object, that has some query set, and still be able to apply JOIN's to it, subqueries and such.

I've search around, and have only found SQL Builder, which seems very basic, and doesn't support joins. Which would be a major feature that would really make it useful.

Upvotes: 4

Views: 8784

Answers (10)

Aaron
Aaron

Reputation: 3814

FluentPDO looks nice if you're already using PDO: https://github.com/envms/fluentpdo

Upvotes: 1

c9s
c9s

Reputation: 1917

Try magsql https://github.com/maghead/magsql, a SQL builder designed for performance written in PHP, comes with join support and cross-platform SQL generation.

It's currently used in the fastest pure PHP orm "maghead"

Upvotes: 0

Günce Bektaş
Günce Bektaş

Reputation: 89

You can use lenkorm it's very easy:

select('contents)->left('categories ON categories.category.id = contents.category_id)->where('content_id = 1')->result();

or you can use as:

select('contents)->left('categories->using(categoru_id)->where('content_id = 1')->result();

Download it from github

Upvotes: -1

Charlie
Charlie

Reputation: 59

I use the query builder from the phptoolcase library, it uses pdo connector, has join support.

http://phptoolcase.com/guides/ptc-qb-guide.html

You can use it with the connection manager fro the library to setup multiple database connection very quickly.

Upvotes: 0

Timo Huovinen
Timo Huovinen

Reputation: 55623

This seems to be a SQL builder with complex join support: http://laravel.com/docs/queries

Upvotes: 0

anonymous
anonymous

Reputation: 11

superfast SQLObject based IteratorQuery from pastaPHP
iterates over resource

 foreach(_from('users u')
   ->columns("up.email_address AS EmailAddress", "u.user_name AS u.UserName")
   ->left('userprofiles up', _eq('u.id', _var('up.id')))
   ->where(_and()->add(_eq('u.is_active',1)))
   ->limit(0,10)
   ->order("UserName")
   ->execute("myConnection") as $user){

   echo sprintf(
          '<a href="mailto:%s">%s</a><br/>', 
          $user->EmailAdress, 
          $user->UserName
   );
 }

Upvotes: 1

Stefan Gehrig
Stefan Gehrig

Reputation: 83622

Zend_Db_Select from the Zend_Db package of the Zend Framework can do such things as:

// Build this query:
//   SELECT p."product_id", p."product_name", l.*
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id
$select = $db->select()
    ->from(array('p' => 'products'), array('product_id', 'product_name'))
    ->join(array('l' => 'line_items'), 'p.product_id = l.product_id');

(from Example 11.54. Example of the join() method in the Zend Framework Manual)

If you don't like to run a full-blown ORM package, this could be the way to go.

Upvotes: 4

James Hall
James Hall

Reputation: 7693

I highly recommend CakePHP. It creates joins for you automatically, based on the associations between tables.

Say if you were writing a blog:

app/model/post.php:

class Post extends AppModel {
  var $hasMany = array('Comment');
}

app/controller/posts_controller.php:

function view($id) {
  $this->set('post', $this->Post->read(null, $id));
}

app/views/posts/view.ctp:

<h2><?php echo $post['Post']['title']?></h2>
<p><?php echo $post['Post']['body']; /* Might want Textile/Markdown here */ ?></p>
<h3>Comments</h3>
<?php foreach($post['Comment'] as $comment) { ?>
  <p><?php echo $comment['body']?></p>
  <p class="poster"><?php echo $comment['name']?></p>
<?php } ?>

That would be all you have to write to view a blog post, your database schema is read and cached. As long as you keep it consistent with the conventions, you don't have to tell cake anything about how your table is set up.

posts:
id INT
body TEXT
created DATETIME

comments:
id INT
body TEXT
name VARCHAR
post_id INT

It has adapters to support MySQL, MSSQL, PostgreSQL, SQLite, Oracle and others. You can also wrap webservices as models, and even get it to do joins between data in your database and remote data! It's very clever stuff.

Hope this helps :)

Upvotes: 1

andyuk
andyuk

Reputation: 39108

I would advise using a PHP framework such as Symfony which uses Propel by default and can use Doctrine if you wish.

CakePHP also uses an ORM, but I don't know which one.

Upvotes: 0

Christian C. Salvad&#243;
Christian C. Salvad&#243;

Reputation: 827366

Maybe you can try an ORM, like Propel or Doctrine, they have a nice programmatic query language, and they return you arrays of objects that represent rows in your database...

For example with Doctrine you can do joins like this:

$q = Doctrine_Query::create();
$q->from('User u')
->leftJoin('u.Group g')
->innerJoin('u.Phonenumber p WITH u.id > 3')
->leftJoin('u.Email e');

$users = $q->execute();

And with Propel:

$c = new Criteria(AuthorPeer::DATABASE_NAME);

$c->addJoin(AuthorPeer::ID, BookPeer::AUTHOR_ID, Criteria::INNER_JOIN);
$c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID, Criteria::INNER_JOIN);
$c->add(PublisherPeer::NAME, 'Some Name');

$authors = AuthorPeer::doSelect($c);

and you can do a lot more with both...

Upvotes: 7

Related Questions