search

dudapiotr / ZfTable

*Not supported*ZfTable 3.1. Awesome ZF2 table / grid (and much much more) generator with huge possibilities of decorating and conditioning. Integrated with DataTables, Doctrine 2, Bootstrap 2.0 and 3.0.
http://dudapiotrek.laohost.net/
MIT License
75 stars 59 forks source link

SQL Join query #6

Closed isurugit closed 10 years ago

isurugit commented 10 years ago

Hi,

Let us know how to work with sql join query for your table module?

When we put a query like this

    $sql = new Sql($this->adapter);
    $select = $sql->select();
    $select->from(array('i' => 'institution')) 
           ->join(array('u' => 'user'),   
            'i.user_id = u.id');

view page shows only the "loading image". At least it doesn't show an error. It just displays the table loading image.

Relationship between Institution table and User table is one to one.

Can you give some advises to us about this?

Thanks.

dudapiotr commented 10 years ago

Hey, If you see loading image, it means that, there is a problem with ajax request. Please look at console (for example : firebug), and send me response from ajax request.

isurugit commented 10 years ago

Hi, Thank you very much for your time. Yes, you are right. I checked this with firebug console. It gives an error. But when I remove the join query like this,

$select = $sql->select(); $select->from(array('i' => 'institution')) ;

it displays columns relevant to institution table. it means this problem is raised with "join" query. Do have any idea about this? I'm grateful to you if you can help me with this issue.

Some code snippets are here,

institution-requests.phtml file.

public function ajaxInstitutionRequestsAction()
{
    $table = new TableExample\InstitutionRequests();
    $table->setAdapter($this->getDbAdapter())
            ->setSource($this->getInstitutionRequests())
            ->setParamAdapter($this->getRequest()->getPost());
    return $this->htmlResponse($table->render());
}
protected $config = array(
    'name' => 'Institution Requests',
    'showPagination' => true,
    'showQuickSearch' => false,
    'showItemPerPage' => true,
    'itemCountPerPage' => 10,
    'showColumnFilters' => true,

);

//Definition of headers
protected $headers = array(

    'address' => array('title' => 'Address' , 'filters' => 'text'),// from institution table
    'name' => array('title' => 'Name') ,//from user table

);
protected function initFilters(\Zend\Db\Sql\Select $query)
{
    if ($value = $this->getParamAdapter()->getValueOfFilter('address')) {
        $query->where("address like '%".$value."%' ");
    }
}
   $select = $sql->select()
         ->from(array('i' => 'institution'))
         ->join(array('u' => 'user'),
                'i.user_id = u.id');

console

Thanks.

dudapiotr commented 10 years ago

Please look at HTML tab (after headers and post) . Have you got something there ?

Additionaly, please send me your code (controller, model ,phtml and table class ). It will help me debug this problem.

isurugit commented 10 years ago

Thanks again for your time. I didn't do a very big thing. Just testing with your codes. I added

*institutionRequestsAction, ajaxInstitutionRequestsAction, getInstitutionRequests functions to TableController.php.(bootom of the class).

Here is the link

https://github.com/isurugit/TestCodes

isurugit commented 10 years ago

Another one thing I couldn't mention to you. Above database was generated by using doctrine2 . But, I don't believe, it is a reason for this issue. Just mention about it. Thanks again for your great support and wonderful module. :)

dudapiotr commented 10 years ago

Hey, Everything is correct. After added all code to my application, I saw wonderfull table:

image

Have you got correct database configuration ? Maybe you forgot to set login / database in config file ? I think the problem is in another place.

Please run this method, and send me result. I got following result: int(1)

public function fetchAllInstitutionRequests(){

    $sql = new Sql($this->adapter);
    $select = $sql->select()
         ->from(array('i' => 'institution'))
         ->join(array('u' => 'user'),
                'i.user_id = u.id');       

    $res = $sql->prepareStatementForSqlObject($select);
    var_dump($res->execute()->count());
    die;

    return $select;

}
isurugit commented 10 years ago

Hi, I tested your code, but I couldn't see anything as an output. After that , this code was run; public function fetchAllInstitutionRequests(){

$sql = new Sql($this->adapter);
$select = $sql->select()
     ->from(array('i' => 'institution'));       

$res = $sql->prepareStatementForSqlObject($select);
var_dump($res->execute()->count());
die;

return $select;

}

but no output , no errors(no errors in firebug console), just a blank page though I remove the "die". After that this code was run, public function fetchAllInstitutionRequests(){

$sql = new Sql($this->adapter);
$select = $sql->select()
     ->from(array('i' => 'institution'));       

return $select;

}

It gives a partial table like this, obviously institution details only.
partial

Anyway, now I know at least something is going wrong with my configurations. I'll update you after resolving this issue. Thanks a lot for your time. :+1:

dudapiotr commented 10 years ago

Ok. I'am waiting for your response. It's really strange if you dont see antything, becouse how You can resolve the problem, if you dont know what is wrong ...

Maybe You havent got enable debugging ?

error_reporting(E_ALL); ini_set('display_errors', 1);

isurugit commented 10 years ago

Hi, Thank you very much for your tips. I re-installed the zend application and wamp server, after that, again I tested your code

$res = $sql->prepareStatementForSqlObject($select);
var_dump($res->execute()->count());
die;

This time it shows int 1 as the out put. int1

But, after removing above lines, it again doesn't show the table, shows only loading image.But firebug console shows an error as ; "NetworkError: 500 Internal Server Error - http://zendapplication/table/ajax-institution-requests"

I don't know whether this issue is my machine related one or not, anyway tomorrow I'm expecting to move to another machine and re-test everything. I'll update you again .

Thanks.

dudapiotr commented 10 years ago

Please look at HTML tab (after headers and post) . Have you got something there ?

isurugit commented 10 years ago

Hi, It gives an error like this, furthermore I'm attaching my sample application herewith, since sometimes you might figure out if I have done any mistake. https://github.com/isurugit/zendapplication/

Sorry for troubling you several times, but at this moment I feel helpless. It would be a great help if you can lend a hand with this issue.

An error occurred An error occurred during execution; please try again later. Additional information: Zend\Db\Adapter\Exception\InvalidQueryException

File:

C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Pdo\Statement.php:245

Message:

Statement could not be executed

Stack trace:

#0 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Paginator\Adapter\DbSelect.php(115): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
#1 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php(881): Zend\Paginator\Adapter\DbSelect->count()
#2 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php(560): Zend\Paginator\Paginator->_calculatePageCount()
#3 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Source\SqlSelect.php(154): Zend\Paginator\Paginator->setItemCountPerPage(10)
#4 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Source\SqlSelect.php(114): ZfTable\Source\SqlSelect->initPaginator()
#5 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Source\SqlSelect.php(89): ZfTable\Source\SqlSelect->getPaginator()
#6 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\AbstractTable.php(166): ZfTable\Source\SqlSelect->getData()
#7 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Row.php(118): ZfTable\AbstractTable->getData()
#8 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Row.php(76): ZfTable\Row->renderRowHtml()
#9 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Render.php(128): ZfTable\Row->renderRows()
#10 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\AbstractTable.php(230): ZfTable\Render->renderTableAsHtml()
#11 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Controller\TableController.php(434): ZfTable\AbstractTable->render()
#12 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Mvc\Controller\AbstractActionController.php(83): ZfTable\Controller\TableController->ajaxInstitutionRequestsAction()
#13 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
#14 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(468): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#15 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(207): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#16 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Mvc\Controller\AbstractController.php(117): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#17 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Mvc\DispatchListener.php(114): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
#18 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
#19 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(468): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#20 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(207): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#21 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Mvc\Application.php(309): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#22 C:\wamp\www\zendapplication\public\index.php(17): Zend\Mvc\Application->run()
#23 {main}

Previous exceptions:

PDOException

File:

    C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Pdo\Statement.php:240

Message:

    SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'

Stack trace:

    #0 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Pdo\Statement.php(240): PDOStatement->execute()
    #1 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Paginator\Adapter\DbSelect.php(115): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
    #2 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php(881): Zend\Paginator\Adapter\DbSelect->count()
    #3 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php(560): Zend\Paginator\Paginator->_calculatePageCount()
    #4 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Source\SqlSelect.php(154): Zend\Paginator\Paginator->setItemCountPerPage(10)
    #5 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Source\SqlSelect.php(114): ZfTable\Source\SqlSelect->initPaginator()
    #6 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Source\SqlSelect.php(89): ZfTable\Source\SqlSelect->getPaginator()
    #7 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\AbstractTable.php(166): ZfTable\Source\SqlSelect->getData()
    #8 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Row.php(118): ZfTable\AbstractTable->getData()
    #9 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Row.php(76): ZfTable\Row->renderRowHtml()
    #10 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Render.php(128): ZfTable\Row->renderRows()
    #11 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\AbstractTable.php(230): ZfTable\Render->renderTableAsHtml()
    #12 C:\wamp\www\zendapplication\vendor\dudapiotr\zftable\src\ZfTable\Controller\TableController.php(434): ZfTable\AbstractTable->render()
    #13 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Mvc\Controller\AbstractActionController.php(83): ZfTable\Controller\TableController->ajaxInstitutionRequestsAction()
    #14 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
    #15 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(468): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
    #16 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(207): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
    #17 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Mvc\Controller\AbstractController.php(117): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
    #18 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Mvc\DispatchListener.php(114): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
    #19 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
    #20 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(468): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
    #21 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(207): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
    #22 C:\wamp\www\zendapplication\vendor\zendframework\zendframework\library\Zend\Mvc\Application.php(309): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
    #23 C:\wamp\www\zendapplication\public\index.php(17): Zend\Mvc\Application->run()
    #24 {main}

Thanks.

dudapiotr commented 10 years ago

The problem is below (duplicate column id - it's interesting, becouse it first time, when i see this error). Always showed the column is ambiguous.

Message:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'

Try this (specify only needed column):

public function fetchAllInstitutionRequests()
{

    $sql = new Sql($this->adapter);
    $select = $sql->select()
            ->from(array('i' => 'institution'), array('address'))
            ->join(array('u' => 'user'), 'i.user_id = u.id', array('name'));

    return $select;
}
isurugit commented 10 years ago

It is working :) . Thank you very much for your great support.

Another small thing that I figured out - Order of the CSS and JS files should be like this; otherwise it doesn't show anything in the table or table headers, this is not related to above issue, but I think this may help to some one who faced a problem like this https://github.com/dudapiotr/ZfTable/issues/1 .

    ->prependStylesheet($this->basePath() . '/css/bootstrap-3.0.0/bootstrap.min.css')      
    ->prependStylesheet($this->basePath() . '/css/style.css')
    ->prependStylesheet($this->basePath() . '/css/zf-table/zf-table.css')  

        ->prependFile($this->basePath() . '/js/DT_bootstrap_3.js')          
        ->prependFile($this->basePath() . '/js/zf-table.js')                     
        ->prependFile($this->basePath() . '/js/jquery.dataTables.min.js')
        ->prependFile($this->basePath() . '/js/jquery.min.js')
        ->prependFile($this->basePath() . '/js/respond.min.js', 'text/javascript', array('conditional' => 'lt IE 9',))
        ->prependFile($this->basePath() . '/js/html5shiv.js',   'text/javascript', array('conditional' => 'lt IE 9',))

Thanks.