Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SubQueries (tables from outside of the current model) #1175

Closed
nowackipawel opened this issue Aug 22, 2018 · 5 comments
Closed

SubQueries (tables from outside of the current model) #1175

nowackipawel opened this issue Aug 22, 2018 · 5 comments
Labels
database Issues or pull requests that affect the database layer

Comments

@nowackipawel
Copy link
Contributor

nowackipawel commented Aug 22, 2018

In one of my models I am trying to pass subquery (another table) to final query i.e.

$subQuery = $this->setTable('a')->select('usr_id')->where...->getCompiledSelect();
$this->db->resetSelect();
$this->setTable('b') //models table
->whereIn('b_usr_id', $subQuery, false)
->get();

but instead of

SELECT * FROM b WHERE b_usr_id IN (SELECT usr_id FROM a WHERE ...)

CI returns:

SELECT * FROM a WHERE b_usr_id IN (SELECT usr_id\n FROM a\n WHERE ...)

(wrong table , additional EOLs)

It looks like we should add to BaseBuilder::resetSelect() 'QBFrom` to resetRun call. I think we should do the same with reset write. Am I right?

I saw there is also $overwrite option
BaseBuilder::from($from, $overwrite = false)
but shuldn't resetSelect clear QBFrom as well?

@jim-parry jim-parry added the database Issues or pull requests that affect the database layer label Dec 10, 2018
@natanfelles
Copy link
Contributor

@nowackipawel What happens if you change this $this->db->resetSelect(); for this $this->resetSelect();?

@nowackipawel
Copy link
Contributor Author

I don't think anything will change....
that's why:

	/**
	 * Get SELECT query string
	 *
	 * Compiles a SELECT query string and returns the sql.
	 *
	 * @param boolean $reset TRUE: resets QB values; FALSE: leave QB values alone
	 *
	 * @return string
	 */
	public function getCompiledSelect($reset = true)
	{
		$select = $this->compileSelect();

		if ($reset === true)
		{
			$this->resetSelect();
		}

		return $select;
	}

as you see there is a $this->resetSelect() call if there is no argument passed to getCompiledSelect() ; (at least in current version)

am I wrong? :-)

@natanfelles
Copy link
Contributor

Before go to the code, my first suspect is that it is not using the same BaseBuilder instance.
That's why I asked. Probably I am wrong.

@nowackipawel
Copy link
Contributor Author

nowackipawel commented Jan 8, 2019

My actual code is not up to date (~ week).
But in this scenario it looks like it is working now/is fixed or sth.

SELECT *
FROM `t_user`
WHERE usr_id IN ('SELECT `usr_id`\nFROM `t_user`\nWHERE `usl_usr_id` = :usl_usr_id:')```

code:
		$subQuery = $this->setTable('t_user_login_attempt')->select('usr_id')->where('usl_usr_id', 1002)->getCompiledSelect();
		$this->resetQuery();
		$this->setTable('t_user')->whereIn('usr_id', $subQuery, false)->get()->getRowObject();

		d($this->db->getLastQuery());die();

protected finalQueryString -> string (108) "SELECT * FROM t_user WHERE usr_id IN ('SELECT usr_id\nFROM t_user\nWHERE ...

SELECT *
FROM `t_user`
WHERE usr_id IN ('SELECT `usr_id`\nFROM `t_user`\nWHERE `usl_usr_id` = :usl_usr_id:')

→protected originalQueryString -> string (47) "SELECT * FROM t_user WHERE usr_id IN :usr_id:"

SELECT *
FROM `t_user`
WHERE usr_id IN :usr_id:


@lonnieezell
Copy link
Member

If you need to access another table from within a model, it's better just to use the db's table() method to get a new builder instance. That keeps the queries separated, which is why that exists now. I got tired of queries leaking onto each other in past versions :)

$this->db->table('b')
->whereIn('b_usr_id', $subQuery, false)
->get()

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
database Issues or pull requests that affect the database layer
Projects
None yet
Development

No branches or pull requests

4 participants