howto left join table in nette orm

Notice: This thread is very old.
bestxp
Member | 6
+
0
-

i have 2 tables like

agency

  • id
  • title
  • date_create

agency_photo

  • id
  • agency_id
  • main
  • server_name

i want
SELECT *, agency_photo.* from agency LEFT JOIN agency.id = agencty_photo.agency_id

do

$connection->table(‘agency’)→ ?? join(not found) where method to join tables?

but there is a feature in the design of the database can not use foreign keys, as you resize it imposes substantial restrictions

How can I specify relationships between tables?
Or, to manually connect them?

A framework for using a test project

hrach
Member | 1819
+
0
-

well, the point is the fact, you dont need any join (since you are not filtering by this join).

foreach ($db->table('agency') as $agency) {
 	foreach ($agency->related('agency_photo') as $photo) {
		$photo->server_name;
	}
}

maybe these slides could help you: http://public.skrasek.com/…_2012_04_28/

bestxp
Member | 6
+
0
-
No reference found for $agency->related(agency_photo).

that is, your example does not solve this problem, and requires the presence of foreign keys. What options are there in the framework for building queries where () → group () → join () → order () → etc, or to be integrated for this purpose an external library?

Last edited by bestxp (2012-09-10 13:04)

duke
Member | 650
+
0
-

bestxp wrote:

How can I specify relationships between tables?

Relationships between tables are resolved through database reflection classes (classes implementing interface Nette\Database\IReflection). Default implementation is Nette\Database\Reflection\DiscoveredReflection which is based on foreign keys defined in the database. There is also Nette\Database\Reflection\ConventionalReflection which is based on naming conventions. If neither of these suits your needs, you can always write your own, specialized one.

bestxp wrote:

Or, to manually connect them?

For special queries, you can use methods Nette\Database\Connection::query or Nette\Database\Connection::exec.

You can also use any PDO methods, because Nette\Database\Connection is an extension of PDO.

With this approach however, you will not be able to use advanced features of Nette\Database classes.

bestxp
Member | 6
+
0
-

something is not clear how to use the interface and where to apply it.
Example of tests / Nette / Database I would like to see me for a class or interface table to implement?

Class Category extends Selection implements iReflection {}?

As strange Nette\Database\Connection::query is just a PDO, that is, again, to write queries hands, not analog Selection

Last edited by bestxp (2012-09-10 17:32)

duke
Member | 650
+
0
-

bestxp wrote:

something is not clear how to use the interface and where to apply it.
Example of tests / Nette / Database I would like to see me for a class or interface table to implement?

Class Category extends Selection implements iReflection {}?

No, you are not supposed to extend Selection. Either use Nette\Database\Reflection\ConventionalReflection or write your own reflection class like this:

class MyDatabaseReflection implements Nette\Database\IReflection
{
	function getPrimary($table)
	{
		// TODO
	}

	function getHasManyReference($table, $key)
	{
		// TODO
	}


	function getBelongsToReference($table, $key);
	{
		// TODO
	}

	function setConnection(Connection $connection)
	{
	}
}

For more info about these methods, see docblock comments here.

As for where to apply it, add a ‘reflection’ field in your nette.database config:
This would retain the default reflection:

nette:
	database:
		default:
			dsn: ...
			user: ...
			password: ...
			reflection: Nette\Database\Reflection\DiscoveredReflection

So you could put there e.g.:

	reflection: Nette\Database\Reflection\ConventionalReflection

or

	reflection: MyDatabaseReflection

bestxp wrote:

As strange Nette\Database\Connection::query is just a PDO, that is, again, to write queries hands, not analog Selection

It is not a mere PDO, but almost. And yes, you bypass Selection class and its features this way. If you want to keep using these features, you need to use proper reflection class for your database.

bestxp
Member | 6
+
0
-

little understood, not exactly convenient, does not specify the reflection of the selected class, and not the whole project?
That is, for example, news categories and classes have their reflection which I refer for example through IoC in __ construct

Although the study will have to look in more detail, but something tells me that all the “nice” does not do it, but only through the crutches.

duke
Member | 650
+
0
-

Sorry, but I have troubles understanding. What news and categories? Classes have their reflections, but that is something completely different than the database reflection classes we are discussing.

If you could use foreign keys, you would simply use DiscoveredReflection. But since you can't you have to provide the same information through alternate way. Either through ConventionalReflection (if your database uses some common naming conventions) or more specific one (which can e.g. be an extension of ConventionalReflection).

I suggest, you experiment some with ConventionalReflection to see if it is sufficient for you, and if it is not, think about how to extend it.

petr.pavel
Member | 504
+
0
-

@hardevine: Please don't hijack threads, create your own. And don't confuse Nette Database with NotORM. You use Nette Database.

Last edited by petr.pavel (2013-04-24 19:31)