trying to create a 'join' statement using Zend DB Table - zend framework 2

Question

I have a model that needs to execute a join query on 2 tables... lets call them friend_list and user_profile.

I am having a heck of a time trying to put together the zend style code to produce the proper query I need to accomplish this... below is the desired query:

SELECT friend_list.friend_id, user_profile.id, user_profile.username
FROM `friend_list`
INNER JOIN `user_profile`
ON friend_list.friend_id = user_profile.id
where user_id = 1

Here is my model_friends

<?php
//model created to add user to database, sendmail etc...
require_once 'Zend/Db/Table/Abstract.php';

class Model_Friends extends Zend_Db_Table_Abstract
{
protected $_name = "friend_list";

public function fetchFriendList($userID)
{

    $accountsTable = array('up' => 'user_profile');
    $select = $this->select()
                   ->from($this->_name)
                   ->join($accountsTable, 'up.id = friend_List.friend_id', array())
                   ->where("up.id = ?", $userID);

    $result = $this->fetchAll($select);


    if ($result !== null){
        echo $select;
        return $result;
    } else {
        echo "no records found";
    }   
  }
}

the above model produces the follow SQL statement which is NOT what I want...

SELECT `friend_list`.* 
FROM `friend_list` 
INNER JOIN `user_profile` 
AS `up` 
ON up.id =     friend_List.friend_id 
WHERE (up.id = '1')

adding the table structures as requested:

DROP TABLE IF EXISTS `buzz`.`friend_list`;
CREATE TABLE  `buzz`.`friend_list` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`friend_id` int(11) NOT NULL,
`approved_timestamp` date NOT NULL,
`status` varchar(15) DEFAULT 'pending',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `buzz`.`user_profile`;
CREATE TABLE  `buzz`.`user_profile` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mob` varchar(50) NOT NULL DEFAULT 'no info',
`dob` varchar(50) NOT NULL DEFAULT '',
`yob` varchar(50) NOT NULL DEFAULT '',
`language` varchar(75) NOT NULL DEFAULT 'English',
`gender` varchar(25) NOT NULL DEFAULT 'no info',
`about` varchar(1000) NOT NULL DEFAULT 'no info',
`country` varchar(45) NOT NULL DEFAULT 'no info',
`username` varchar(45) NOT NULL,
PRIMARY KEY (`id`,`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

well I just tested the query which is generated by the Zend code and it looks perfect.. ->where("up.id = ?", $userID); is correct and there is no difference between these queries. All you need to do in the select list u just specify the colum name as friend_list.colname,friend_list.col2....etc — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 2, 2014 at 15:31
@AbhikChakraborty - the output from the ZEND query is different then what I am expecting. hence the reason why I supplied my desired SQL query. — Frank Castle
– Frank Castle, Commented Apr 2, 2014 at 15:39

Tim Burch · Accepted Answer · 2014-04-02 17:45:55Z

1

Try changing your Zend_Db_Select object to the following:

$select = $this->select()
    ->join($accountsTable, 'friend_list.friend_id = user_profile.id', array())
    ->where('user_profile.id = ?', $userID)
    ->reset('columns')
    ->columns(array('friend_list.friend_id', 'user_profile.id', 'user_profile.username'));

answered Apr 2, 2014 at 17:45

Tim Burch

1,0937 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frank Castle Over a year ago

THANK YOU... I am going to edit your answer slightly to include the setintegrity(false)... but this worked

dixromos98 · Accepted Answer · 2014-04-03 05:37:14Z

1

This is not an answer to the question but since i cant comment yet i will post this here. I found the following website helpful with the join examples.

github.com

answered Apr 3, 2014 at 5:37

dixromos98

7545 silver badges18 bronze badges

Comments

Frank Castle · Accepted Answer · 2014-04-02 19:25:13Z

the end result of my model_friends script is as follows:

<?php
//model created to add user to database, sendmail etc...
require_once 'Zend/Db/Table/Abstract.php';

class Model_Friends extends Zend_Db_Table_Abstract
{
protected $_name = "friend_list";

public function fetchFriendList($userID)
{       
    $select = $this->select()
                   ->from($this)
                   ->setIntegrityCheck(false)
                   ->join(array('u'=>'user_profile'), 'friend_list.friend_id =u.id', array())
                   ->columns(array('u.id', 'u.username'))
                   ->where("friend_list.user_id = ?", $userID);     

    $result = $this->fetchAll($select);                    
    if ($result !== null){
        echo $select;
        return $result;
    } else {
        echo "no records found";
    }   
  }
}

Collectives™ on Stack Overflow

trying to create a 'join' statement using Zend DB Table - zend framework 2

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related