Compound primary-key not supported

[diegoldev]

Describe the bug
Trying to set a model with compound primary-key (two or more columns) is not supported by Codeigniter Model and DB Builder

CodeIgniter 4 version
4.0.0.beta.3

Affected module(s)
Codeigniter Model and DB Builder

Expected behavior, and steps to reproduce if appropriate

1. Define a compound primary-key table. (E.g. book_author which pk be: book_id,author_id)
2. Set $this->primaryKey as array ['book_id', 'author_id']
3. Try to get a row with $this->find()
4. An Exception is thrown because 'array cannot be parsed as a string'

Context

• OS: Debian 9
• Web server: Apache 2
• PHP version 7.3

[nowackipawel]

I did that in my copy of CI4 cuz I needed it.
In fact, I did it by extending the Model class but only for insert / update method with a little dirty code.

As for now, CI4 has been implemented (as I understood from PRs, issues comments) with no support for compound PKs. :(

[diegoldev]

@nowacki sure, I could do by myself but I think that should be supported in a future since other way the framework would force you to use not normalized database/table.

[nowackipawel]

@Rino7 - that's true :(
OFC I could share my solution, but it is dirty and I am not proud about if even if it works well.

[diegoldev]

@nowackipawel Will appreciate if you could share your code with me

[nowackipawel]

Pls, send me remainder to my email (you will find mine on my profile page). I cannot find yours on your profile page. I will share it in next few days (maybe you made me that way to improve sth, but I don't think so;-)), but please do not except hi-quality code I wrote it to get basic support for that kinda PKs.

[nowackipawel]

I have it done with:

	/**
	 * @param  $id integer|string|array|null:
	 *         int|string:  WHERE $this->table.$this->primaryKey = $id
	 *         indexed array: WHERE $this->table.$this->primaryKey IN ([2,3]) <i.e.: $id = [2,3]>
	 *         associative array: WHERE $this->table.key1 = 1 AND $this->table.key2 IN([2,3]) <i.e.: $id = [key1 => 1, key2 => [2,3]]>
	 * @return BaseBuilder
	 */
	private function parseId($id = null)
	{
		$builder = $this->builder();

		// is numeric or is non empty string
		if (! empty($id))
		{
			if (is_numeric($id) || is_string($id))
			{
				$builder = $builder->where($this->table . '.' . $this->primaryKey, $id);
			}
			elseif (is_array($id))
			{
				//$id contains an indexed array
				if (array_keys($id) === range(0, count($id) - 1))
				{
					$builder = $builder->whereIn($this->table . '.' . $this->primaryKey, $id);
				}
				//$id contains associative array
				else
				{
					foreach ($id as $key => $value)
					{
						if (! empty($value) && is_array($value))
						{
							$builder = $builder->whereIn($this->table . '.' . $key, $value);
						}
						else
						{
							$builder = $builder->where($this->table . '.' . $key, $value);
						}
					}
				}
			}
		}

		return $builder;
	}

and some modifications in find, update, delete methods.

This limitation was so irritating to me.

[nowackipawel]

And i.e. at the beginning of delete() or update() method of Model I have:

		$builder = $this->parseId($id);

instead of:

DELETE removed and replaced with above parseId call:
		if (! empty($id) && is_numeric($id))
		{
			$id = [$id];
		}

		$builder = $this->builder();
		if (! empty($id))
		{
			$builder = $builder->whereIn($this->primaryKey, $id);
		}

UPDATE1 removed part one:

		if (is_numeric($id) || is_string($id))
		{
			$id = [$id];
		}

UPDATE2 removed part two and replaced with call showed at top of this message:
		$builder = $this->builder();

		if ($id)
		{
			$builder = $builder->whereIn($this->table . '.' . $this->primaryKey, $id);
		}

the rest stayed the same.
In this approach we do not ha

[nowackipawel]

Additionally, there is easy option to modify find() method to new version (I am making this in the fly):
instead of this:

public function find($id = null)
	{
		$builder = $this->builder();

		if ($this->tempUseSoftDeletes === true)
		{
			$builder->where($this->table . '.' . $this->deletedField, null);
		}

		if (is_array($id))
		{
			$row = $builder->whereIn($this->table . '.' . $this->primaryKey, $id)
					->get();
			$row = $row->getResult($this->tempReturnType);
		}
		elseif (is_numeric($id) || is_string($id))
		{
			$row = $builder->where($this->table . '.' . $this->primaryKey, $id)
					->get();

			$row = $row->getFirstRow($this->tempReturnType);
		}
		else
		{
			$row = $builder->get();

			$row = $row->getResult($this->tempReturnType);
		}

		$eventData = $this->trigger('afterFind', ['id' => $id, 'data' => $row]);

		$this->tempReturnType     = $this->returnType;
		$this->tempUseSoftDeletes = $this->useSoftDeletes;

		return $eventData['data'];
	}

to:

	public function find($id = null)
	{
		$builder = $this->parseId();

		if ($this->tempUseSoftDeletes === true)
		{
			$builder->where($this->table . '.' . $this->deletedField, null);
		}

		if (is_array($id))
		{
			$row = $builder->get()->getResult($this->tempReturnType);
		}
		elseif (is_numeric($id) || is_string($id))
		{
			$row = $builder->get()->getFirstRow($this->tempReturnType);
		}
		else
		{
			$row = $builder->get()->getResult($this->tempReturnType);
		}

		$eventData = $this->trigger('afterFind', ['id' => $id, 'data' => $row]);

		$this->tempReturnType     = $this->returnType;
		$this->tempUseSoftDeletes = $this->useSoftDeletes;

		return $eventData['data'];
	}

there should be no breaking changes. of course it would be good to check this on the current version , this is working 100% good on 4.0.3