search

yiisoft / yii2

Yii 2: The Fast, Secure and Professional PHP Framework
http://www.yiiframework.com
BSD 3-Clause "New" or "Revised" License
14.24k stars 6.91k forks source link

Problems with ActiveDataProvider // Pagination&Counting dont work correctly with relation condition #4529

Closed ItsReddi closed 10 years ago

ItsReddi commented 10 years ago

Following ActiveDataProvider:

    public function getLinkprovider(){
        $query = ImportLink::find()->where("t_importFiles_id = $this->id")->with([
                     'tImportFilesId1' => function($query) {
                        $query->andWhere('duplicate = 0'); //this will be ignored
                      },
                    ])->orderBy('`match` DESC,t_importFiles_id1');
        $dataProvider = new ActiveDataProvider([
                'query' => $query,
                ]);
        return $dataProvider;
    }

the problem is, that the "IN" statement will use the '$query->andWhere('duplicate = 0');' correctly.

But the Count SQL + the result SQL ignoring it.

So...

  1. Count statement count = 20;
  2. result Statement count = 20;
  3. the in statement count for example = 17 because 3 fields are marked as duplicate

this causes empty gridlines and/or exceptions.

samdark commented 10 years ago

Can you provide SQL for creating tables you're using, some data and models (if these are different from the ones generated by Gii)?

ItsReddi commented 10 years ago

i tried to shorten it a bit. 1.

CREATE TABLE IF NOT EXISTS `bildarchiv`.`t_importFiles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `shortdesc` TEXT NULL,
  `longdesc` TEXT NULL,
  `crc` VARCHAR(40) NULL,
  `xserve_path` VARCHAR(500) NOT NULL,
  `meta_data` TEXT NOT NULL,
  `status` INT NOT NULL DEFAULT 0 COMMENT 'Welchen Status im Import',
  `duplicate` INT NOT NULL DEFAULT 0 COMMENT 'als duplikat markiert\n',
  `imported` INT NOT NULL DEFAULT 0 COMMENT 'im neuen Bildarchiv angewendet',
  `should_deleted` INT NOT NULL DEFAULT 0 COMMENT 'soll gelöscht werden',
  `is_parent` INT NOT NULL DEFAULT 0,
  `created` DATETIME NULL,
  `normalized_name` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  INDEX `crc` (`crc` ASC),
  INDEX `normalized_name` (`normalized_name` ASC),
  INDEX `name` (`name` ASC))
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `bildarchiv`.`t_importFiles_has_t_importFiles` (
  `t_importFiles_id` INT NOT NULL,
  `t_importFiles_id1` INT NOT NULL,
  `match` FLOAT NOT NULL,
  PRIMARY KEY (`t_importFiles_id`, `t_importFiles_id1`),
  INDEX `fk_t_importFiles_has_t_importFiles_t_importFiles2_idx` (`t_importFiles_id1` ASC),
  INDEX `fk_t_importFiles_has_t_importFiles_t_importFiles1_idx` (`t_importFiles_id` ASC),
  INDEX `match` (`match` ASC),
  CONSTRAINT `fk_t_importFiles_has_t_importFiles_t_importFiles1`
    FOREIGN KEY (`t_importFiles_id`)
    REFERENCES `bildarchiv`.`t_importFiles` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_t_importFiles_has_t_importFiles_t_importFiles2`
    FOREIGN KEY (`t_importFiles_id1`)
    REFERENCES `bildarchiv`.`t_importFiles` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Data for 1: (; seperated)

id;name;shortdesc;longdesc;crc;xserve_path;meta_data;status;duplicate;imported;should_deleted;is_parent;created;normalized_name
40;Trauben-Sanddorn-Müsli_leicht_rub.JPG;NULL;;fec3b63653bbf53697c2b604dfdf4cb5;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Trauben-Sanddorn-Müsli_leicht_rub.JPG;"300;4580;3053;JPG";3;0;0;0;0;"2014-07-10 09:53:15";,trbn,snddrn,msl,lcht,
41;Waldfrucht-Müsli.jpg;NULL;;fcb1c416928fd00588a0159f54ece7ad;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Waldfrucht-Müsli/Waldfrucht-Müsli.jpg;"72;508;363;jpg";3;0;0;0;0;"2014-07-10 09:53:15";,wldfrcht,msl,
43;Waldfrucht-Müsli.tif;NULL;;558cf79d861e75da0a1d1c5f66e3e24d;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Waldfrucht-Müsli/Waldfrucht-Müsli.tif;"300;2117;1512;tif";3;0;0;0;0;"2014-07-10 09:53:15";,wldfrcht,msl,
44;Waldfrucht-Müsli.qxp;NULL;;4faeb14c0a7082b1a25bc781e43db452;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Waldfrucht-Müsli/Waldfrucht-Müsli.qxp;"identify error";100;0;0;0;0;"2014-07-10 09:53:15";,wldfrcht,msl,
55;RHABARBER_MUESLI.TIF;NULL;;4039d6cbf6a0d1be9db7e3781eb83890;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Müsli_kölln/RHABARBER_MUESLI.TIF;"240;1830;1455;TIF";3;0;0;0;0;"2014-07-10 09:53:15";,rhbrbr,msl,
56;"Geringeltes Müsli_leicht.TIF";NULL;;5ecd18308aad94bf8f02e5af24cd4f23;"/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Müsli_kölln/Geringeltes Müsli_leicht.TIF";"240;1998;1431;TIF";3;0;0;0;0;"2014-07-10 09:53:15";,grnglts,msl,lcht,
58;"Aprikosen-Müsli mit Walnuss-Zimt-Crunch_439 kcal leicht.jpg";NULL;;afff60173b0b5f01900186b048a591a2;"/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Müsli_kölln/Aprikosen-Müsli mit Walnuss-Zimt-Crunch_439 kcal leicht.jpg";"300;4580;3053;jpg";3;0;0;0;0;"2014-07-10 09:53:15";,prksn,msl,wlnss,zmt,crnch,kcl,lcht,
74;KOELLN_MUESLI_ORIENTAL.jpg;NULL;;98c67ac3dddf1693b7f64692b1e1f65e;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Müsli_kölln/KOELLN_MUESLI_ORIENTAL.jpg;"304;1278;1000;jpg";3;0;0;0;0;"2014-07-10 09:53:15";,klln,msl,rntl,
76;Heidelbeer-Bananen-Müsli_leicht_rub.JPG;NULL;;04c6e05599abc49f638fb34746a4187e;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Müsli/Heidelbeer-Bananen-Müsli_leicht_rub.JPG;"300;4580;3053;JPG";3;0;0;0;0;"2014-07-10 09:53:15";,rbhdlbr,bnnn,msl,lcht,
120;Blaubeer-Kokos-Müsli_WBANA.pdf;NULL;;930c9c1b8c32aff190081c574736864b;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Blaubeer-Kokos-Müsli_WBANA.pdf;Undefined;99;0;0;0;0;"2014-07-10 09:53:15";,blbr,kks,msl,wbn,
125;"buttermilch-pancakes mit orangen-mandarinen-kompott_XS1110_BONNE_MAM_1703576_leicht.jpg";NULL;;f9f3f67794644e65963fd2b864e47259;"/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/buttermilch-pancakes mit orangen-mandarinen-kompott_XS1110_BONNE_MAM_1703576_leicht.jpg";"400;5386;4042;jpg";3;0;0;0;0;"2014-07-10 09:53:15";,bttrmlch,pncks,rngn,mndrnn,kmptt,bnn,lcht,
163;Wellness-Müsli.jpg;NULL;;c2bff352cb68e47e9b3573058b778fb5;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/1-Frühstück/Wellness-Müsli/Wellness-Müsli.jpg;"72;508;363;jpg";3;0;0;0;0;"2014-07-10 09:53:15";,wllnss,msl,
164;Wellness-Müsli.qxp;NULL;;2ffd0c3fe7f7a54cdb79f9bcc33fec1b;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/1-Frühstück/Wellness-Müsli/Wellness-Müsli.qxp;"identify error";100;0;0;0;0;"2014-07-10 09:53:15";,wllnss,msl,
165;Wellness-Müsli.tif;NULL;;15892b9b612f56bd9c5d999bda16a80c;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/1-Frühstück/Wellness-Müsli/Wellness-Müsli.tif;"300;2115;1512;tif";3;0;0;0;0;"2014-07-10 09:53:15";,wllnss,msl,
175;Blaubeer-Kokos-Müsli_WBANA.jpg;NULL;;6f5bcb099cb7f203f866e05a441787a6;/bildarchiv_xserve/REZEPTARCHIV/FRUEHSTUECK/Blaubeer-Kokos-Müsli_WBANA.jpg;"300;5440;4080;jpg";3;0;0;0;0;"2014-07-10 09:53:15";,rbblbr,kks,msl,wbn,
847;Ingwer-Orangen-Schnitzel_rub.jpg;NULL;;83694362eebfae442c4b89ea4f8e3260;/bildarchiv_xserve/REZEPTARCHIV/FLEISCH/Schnitzel/Ingwer-Orangen-Schnitzel_rub.jpg;"304;2439;2997;jpg";3;0;0;0;0;"2014-07-10 09:53:15";,rbngwr,rngn,schntzl,
943;"30:52 Rindersteak mit Zwiebel-Orangen-Chutney_auch 1228141 burda.jpg";NULL;;9ec64ddfd49b4710889229f43626f21d;"/bildarchiv_xserve/REZEPTARCHIV/FLEISCH/Schnitzel/Steak/30:52 Rindersteak mit Zwiebel-Orangen-Chutney_auch 1228141 burda.jpg";"300;2126;1417;jpg";3;0;0;0;0;"2014-07-10 09:53:15";,rndrstk,zwbl,rngn,chtny,ch,brd,
949;"1228141 burda_butaris_rindersteak mit zwiebel-orangen-chutney.tiff";NULL;;5bc2a9c6988cd87695478b7a58945619;"/bildarchiv_xserve/REZEPTARCHIV/FLEISCH/Schnitzel/Steak/1228141 burda_butaris_rindersteak mit zwiebel-orangen-chutney.tiff";"72;602;464;tiff";3;0;0;0;0;"2014-07-10 09:53:15";,brd,btrs,rndrstk,zwbl,rngn,chtny,
1237;"30:52 Rindersteak mit Zwiebel-Orangen-Chutney_auch 1228141 burda.jpg";NULL;;9ec64ddfd49b4710889229f43626f21d;"/bildarchiv_xserve/REZEPTARCHIV/FLEISCH/Rind/30:52 Rindersteak mit Zwiebel-Orangen-Chutney_auch 1228141 burda.jpg";"300;2126;1417;jpg";3;1;0;0;0;"2014-07-10 09:53:16";,rndrstk,zwbl,rngn,chtny,ch,brd,
1574;"Festliche Bratwurst an Orangen-Rotkraut_Gutfried.pdf";NULL;;71ebf47f160964c1e110f58978c8c51f;"/bildarchiv_xserve/REZEPTARCHIV/FLEISCH/Wurst/Festliche Bratwurst an Orangen-Rotkraut_Gutfried.pdf";Undefined;99;0;0;0;0;"2014-07-10 09:53:16";,fstlch,brtwrst,rngn,rtkrt,gtfrd,

data for 2:

t_importFiles_id;t_importFiles_id1;match
17;40;25
17;41;25
17;43;25
17;44;25
17;55;25
17;56;25
17;58;25
17;74;25
17;76;25
17;120;25
17;125;25
17;163;25
17;164;25
17;165;25
17;175;25
17;847;25
17;943;25
17;949;25
17;1237;25
17;1574;25

Model1:

<?php

namespace common\models;

use Yii;
use yii\data\ActiveDataProvider;
use yii\web\View;
use common\models\ItemPicture;

/**
 * This is the model class for table "t_importFiles".
 *
 * @property integer $id
 * @property string $name
 * @property string $shortdesc
 * @property string $longdesc
 * @property string $crc
 * @property string $xserve_path
 * @property string $meta_data
 * @property integer $status
 * @property integer $duplicate
 * @property integer $imported
 * @property integer $should_deleted
 * @property integer $is_parent
 * @property string $created
 * @property string $normalized_name
 *
 * @property ImportFilesHasTImportFiles[] $importFilesHasTImportFiles
 */
class Import extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 't_importFiles';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['name', 'xserve_path', 'meta_data'], 'required'],
            [['shortdesc', 'longdesc', 'meta_data'], 'string'],
            [['status', 'duplicate', 'imported', 'should_deleted', 'is_parent'], 'integer'],
            [['created'], 'safe'],
            [['name', 'normalized_name'], 'string', 'max' => 255],
            [['crc'], 'string', 'max' => 40],
            [['xserve_path'], 'string', 'max' => 500]
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'name' => 'Name',
            'shortdesc' => 'Kurzbeschreibung',
            'longdesc' => 'Text / Artikel',
            'crc' => 'Crc',
            'xserve_path' => 'Bildarchiv Pfad',
            'meta_data' => 'Meta Daten',
            'status' => 'Welchen Status im Import',
            'duplicate' => 'als duplikat markiert
',
            'imported' => 'im neuen Bildarchiv angewendet',
            'should_deleted' => 'soll gelöscht werden',
            'is_parent' => 'Is Parent',
            'created' => 'Created',
            'normalized_name' => 'Normalized Name',
            'counter' => 'Gefundene Bilder ü. 50%'
        ];
    }

    public function getLinkprovider(){
        $query = ImportLink::find()->where("t_importFiles_id = $this->id")->with([
                     'tImportFilesId1' => function($query) {
                        $query->andWhere('duplicate = 0');
                      },
                    ])->orderBy('`match` DESC,t_importFiles_id1');
        $dataProvider = new ActiveDataProvider([
                'query' => $query,
                ]);
        return $dataProvider;
    }

    public function getGridcolumns(){
        $columns = [
'tImportFilesId1.id',
        /*[
        'header'=>'Vorschau',
                'format'=>'raw',
                        'value'=>function($data){return $data->tImportFilesId1->actions; },
                        ],
            [
                'header'=>'Datei Informationen',
                'format'=>'raw',
                'value'=>function($data){return $data->tImportFilesId1->getPictureInformation($data->match); },
            ],*/
        ];
        return $columns;
    }

}
  1. Model
<?php

namespace common\models;

use Yii;

/**
 * This is the model class for table "t_importFiles_has_t_importFiles".
 *
 * @property integer $t_importFiles_id
 * @property integer $t_importFiles_id1
 * @property double $match
 *
 * @property ImportFiles $tImportFiles
 * @property ImportFiles $tImportFilesId1
 */
class ImportLink extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 't_importFiles_has_t_importFiles';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['t_importFiles_id', 't_importFiles_id1', 'match'], 'required'],
            [['t_importFiles_id', 't_importFiles_id1'], 'integer'],
            [['match'], 'number']
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            't_importFiles_id' => 'T Import Files ID',
            't_importFiles_id1' => 'T Import Files Id1',
            'match' => 'Match',
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getTImportFiles()
    {
        return $this->hasOne(Import::className(), ['id' => 't_importFiles_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getTImportFilesId1()
    {
        return $this->hasOne(Import::className(), ['id' => 't_importFiles_id1']);
    }
}

Good luck.

ItsReddi commented 10 years ago

By the way. I know that: 

$query = ImportLink::find()->where("t_importFiles_id = $this->id")->leftJoin('t_importFiles','t_importFiles_id1 = id')->andWhere('duplicate = 0')->orderBy('`match` DESC,t_importFiles_id1');

would work... but its not nice to define the relations again and again.

cebe commented 10 years ago

you can use joinWith(...) for this.