How to select with WHERE

5 Posts

wxmaper Reply #1, 8 years, 11 months ago

Table 1

CREATE TABLE IF NOT EXISTS `modx_profut_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `role` int(11) DEFAULT '0',
  `team_id` int(11) DEFAULT '0',
  `league_id` int(11) DEFAULT '0',
  `way_id` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=451 DEFAULT CHARSET=utf8;

and Table 2

CREATE TABLE IF NOT EXISTS `modx_profut_teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `logo` text NOT NULL,
  `tagline` text NOT NULL,
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=90 DEFAULT CHARSET=utf8;

This tables linked by fields modx_profut_users.team_id = modx_profut_teams.id, but a few users may have the same team_id.

How i select all rows from modx_profut_teams where modx_profut_users have a league_id = 1?

For get all rows without criterias i'm use this code:

$c = $modx->newQuery('ProfutTeams');

i try to add

$c->leftJoin('ProfutUsers', 'ProfutUsers', 'ProfutUsers.league_id = 1');

but this don't work ;(

5,888 Posts

Bruno17 Reply #2, 8 years, 11 months ago

$c = $modx->newQuery('ProfutTeams');
$c->leftJoin('ProfutUsers', 'ProfutUsers', 'ProfutUsers.team_id = ProfutTeams.id');
$c->where(array('ProfutUsers.league_id'=>1));

but you should really define the relations within your xPDO-schema to get all the power out of xPDO

and maybe you get easier done, what you are after with existing snippets, for example migxLoopCollection of the MIGX - Extra or pdoResources of pdoTools

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

5 Posts

wxmaper Reply #3, 8 years, 11 months ago

Quote from: Bruno17 at Nov 06, 2017, 11:35 PM

$c = $modx->newQuery('ProfutTeams');
$c->leftJoin('ProfutUsers', 'ProfutUsers', 'ProfutUsers.team_id = ProfutTeams.id');
$c->where(array('ProfutUsers.league_id'=>1));

I tried this code before and it also does not work

Quote from: Bruno17 at Nov 06, 2017, 11:35 PM

but you should really define the relations within your xPDO-schema to get all the power out of xPDO

How?
Maybe this will help, there may be several users with the same team_id, but only one will have the role = 1
and i tried this code:

$c->leftJoin('ProfutUsers', 'ProfutUsers', 'ProfutUsers.team_id = ProfutTeams.id, ProfutUsers.role = 1');
$c->where(array('ProfutUsers.league_id'=>1));

and:

$c->leftJoin('ProfutUsers', 'ProfutUsers', 'ProfutUsers.team_id = ProfutTeams.id');
$c->where(array('ProfutUsers.league_id'=>1,'ProfutUsers.role'=>1));

... all this does not work correctly [ed. note: wxmaper last edited this post 8 years, 11 months ago.]

5,888 Posts

Bruno17 Reply #4, 8 years, 11 months ago

does not work

??

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!