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

Can't set subquery as WHERE condition. #1775

Closed
nowackipawel opened this issue Mar 1, 2019 · 2 comments
Closed

Can't set subquery as WHERE condition. #1775

nowackipawel opened this issue Mar 1, 2019 · 2 comments

Comments

@nowackipawel
Copy link
Contributor

(new \ExampleModel)->where('(SELECT COUNT(*) FROM t1 WHERE t1_exa_id = ex_id)',10);

The reason is in whereHaving() method:

				$op = $this->getOperator($k);
				$k  = trim(str_replace($op, '', $k)); //here...

I am wondering if we could introduce method which allows us to turn off filtering operators from "field" names.

I think the solution will be:

  1. changing whereHavingMethod():

				$op = $this->getOperator($k);
				if($escapeField)
				{
					$k  = trim(str_replace($op, '', $k));
				}
  1. changing all whereX() methods like this:
	public function where($key, $value = null, $escape = null, $escapeField = true)
	{
		return $this->whereHaving('QBWhere', $key, $value, 'AND ', $escape, $escapeField);
	}

or intruduce new method like escapeFields(bool $escape = true)


	/**
	 * If operators are existed in names of fields should be deleted or not.
	 *
	 * @var bool
	 */
	protected $escapeFields = true;

	/**
	 * Allows to turn off deleting operators from field names.
	 * @param bool $escape
	 * @return $this
	 */
	public function setEscapeFields(bool $escape = true)
	{
		$this->escapeFields = $escape;
		return $this;
	}
@jim-parry jim-parry added this to the 4.0.0-beta.3 milestone Mar 25, 2019
@atishhamte
Copy link
Contributor

There is a different way to set the subquery in CodeIgniter.
This is the way to do so,

$subQuery = $this->db->table('job')
		   ->select('id')
		   ->where('name', 'Developer')
		   ->getCompiledSelect();
		
$jobs = $this->db->table('job')
                 ->where('id not in (' . $subQuery . ')', null, false)
                 ->get()
                 ->getResult();

In the above example, you can see the subquery is mentioned in the where condition, where $value parameter is null and $escape is false.

related to this, I have created the test cases for the same. Refer #1906.

@lonnieezell
Copy link
Member

Or through query grouping.

lonnieezell added a commit that referenced this issue Apr 3, 2019
SubQuery related test cases w.r.t #1775
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants