Problem with Nette ORM and custom convention

ardimo
Member | 5
+
0
-

Edit: Better explaination with diagram in this post.

Problem with Nette ORM and custom convention

Hello! A few weeks ago i decided that Nette is the framework that fits best for the project i am on. I checked a few frameworks based on my needs and settled with Nette. I want to exchange some crude written classes for creating Queries with a clean approach with Nette. So far i am happy and i am in the process of creating a class, that dynamically creates queries based on a few parameters. But for a few hours now i am failing to establish my custom convention-class – which i need, because i have to deal with an existing Database.

I hope someone here can give me a hint on how to proceed here.

On to the details:

Involved in the Problem are basically two tables with various columns:

  • TreeContainer (Has a column “Tree” with an ID and “Container” with an ID)
  • TreePath (Has a column “Tree”, “Parent” and a column “TreePath” all of them are also just IDs)

A lot of other tables and columns are involved, but there are not needed right now to describe my problem.

The Query i want to “recreate” with Nette is the following:

SELECT Container FROM TreeContainer INNER JOIN TreePath ON TreePath.Tree = TreeContainer.Tree WHERE TreePath.Parent = 123456

As the old Database structure has no consistent way of discovering foreign keys based on a given convention i soon discovered that i am not able to use the two provided classes “DiscoveredConventions” or “StaticConventions”. So i went on and created my own class:

<?php

namespace baukasten\ccdb;

use Exception;
use Nette\Database\Conventions\AmbiguousReferenceKeyException;
use Nette\Database\IConventions;

class BKSNetteConventions implements IConventions {

  /**
   * Mapping array.
   * Structure: Source-Table-Name > Target-Table-Name > Column.Target-Table-Name
   *
   * @var string[][]
   */
  private $foreign_keys = array(
    'TreeContainer' => array(
      'TreePath' => 'TreePath.Tree',
    )
  );

  /**
   * Returns primary key for table.
   * In the Database the primary key is allways named exactly like the Table-Name itself. This is why I can just
   * return the table name without any logic.
   *
   * @param string
   *
   * @return string|array|null
   */
  public function getPrimary($table) {
    return $table;
  }

  /**
   * Returns referenced table & referenced column.
   */
  public function getHasManyReference($table, $key) {
    /* Basically the same as getBelongsToReference hence not included here to keep the example short */
  }

  /**
   * Returns referenced table & referencing column.
   * Example
   *     (book, author)      returns array(author, author_id)
   *     (book, translator)  returns array(author, translator_id)
   *
   * @param string $table source table
   * @param string $key   referencing key
   *
   * @return array|null   array(referenced table, referencing column)
   * @throws Exception
   */
  public function getBelongsToReference($table, $key) {
    echo "<pre>Request for Table '{$table}' and key '{$key}' </pre>";
    if (
      !array_key_exists($table, $this->foreign_keys) or
      !array_key_exists($key, $this->foreign_keys[$table])
    ) {
      throw new Exception(
        "Reference to Table '{$table}' and/or Key '{$key}' is missing for function 'getBelongsToReference()'. " .
        'Please evalueate or add the needed References to the configuration-array in BKSNetteConventions.'
      );
    }

    if ($this->foreign_keys[$table][$key] == null) {
      return array(null, null);
    }

   return explode('.', $this->foreign_keys[$table][$key]);
  }
}

And the bits of the code generating the Query:

$table_tree_container = $this->database_explorer->table('Tree_Container');
$table_tree_container->select('Container')->where('TreePath.Parent', $this->prepareValuesForQuery($treenode));

This code is resulting Nette requesting the getBelongsToReference() function with the parameters ($table = ‘Tree_Container’, $key = ‘TreePath’).

My function returns array(‘TreePath’, ‘Tree’). The resulting Query looks like this:

SELECT `Container` FROM `TreeContainer` LEFT JOIN `TreePath` ON `TreeContainer`.`Tree` = `TreePath`.`TreePath` WHERE (`TreePath`.`Parent` = ?)

Looks not bad at the first glance, BUT: “TreeContainer.Tree = TreePath.TreePath” should be “TreeContainer.Tree = TreePath.Tree”.

And i have absolutely no clue how to achiev this and unfortunately the documentation is not very detailed about what the functions getBelongsToReference() and getHasManyReference() exactly do … or I am to stupid to get the logic here.

Maybe someone can point me into the right direction …

Last edited by ardimo (2021-09-16 16:59)

ardimo
Member | 5
+
0
-

Ok, I´ve debugged further and found out, that during the SQL-assembly the function getPrimary() gets called and returns the primary key for ‘TreePath’ – which is correctly ‘TreePath’. But this is not the Key to connect the two tables ‘TreeContainer’ and ‘TreePath’ → they are connected by the same key ‘Tree’ which is neither the primary key of either of the tables involved.

A quick hack was to return a “wrong” key here:

public function getPrimary($table) {
  echo "<pre>Request primary key of Table '{$table}'</pre>";
  if ($table == 'TreePath') {
    return null;
  }
   return $table;
}

Nice hack to prove that it generally could work, but not more, I want a proper solution of course.

I am now in the Process of going in another direction and try not to reimplement the convention-class by myself, but the structure class and use the auto-discovery convention. I am trying to “enhance” the original “Structure” class by trying to inject that piece of information that is missing from the crappy old database structure: ‘belongsTo’.

My problem in general: There are next to no phpdoc descriptions on the functions – often just describing the function's name … So It's hard to figure out what the function actually does and what is expected to be returned.

Any ideas on how to provide the correct information within the Structure class to pass the missing information “manually”?

ardimo
Member | 5
+
0
-

Ultimately my question comes down to this:

I have a database with tables that are not well made to be “autodiscovered” so i need a solution to provide the structure manually.

I think this can be done either through a custom convention or structure. Can someone give me some basic explaination on how to do this? Is it even possible with Nette?

Thanks in advance.

dsar
Backer | 36
+
0
-

For what I understood, you are trying to achieve something to fetch tree-based structure easily.

In my opinion doing that via conventions is hard and not very flexible.

An ideal way is a direct support on Nette Explorer side. When I started with Nette I had modified Nette Explorer so that it has TreeSelection (that extends Selection) and a syntactic sugar ->tree() (instead of ->table()) with methods like ->getParent(), ->getChildren() and so on.

Unfortunately for time reason I couldn't continue it (code that deals with tree structures is long and complex, just look what Doctrine Extension does or CakePHP with TreeBehavior), but imho it's the right way and unfortunately it requires direct support on Nette Explorer.

ardimo
Member | 5
+
0
-

Thank you for your answer!

No, its not about that. The Data for the tree stuff is prepared to be read without being traversed etc. That part is ok.

At the moment I just need a way to tell Nette how the different Tables are connected to each other because any auto-discovery will not work because

  1. there is no metadata configured within the database, so Nette can´t just read the foreign keys and
  2. there is no obvious way (column-name = table-name) to connect the data for Nette without a manual intervention.

Also, a more detailed description of the functions getHasManyReference() and getBelongsToReference() would helpful for me – for example, what they do and what is expected to be returned.

Last edited by ardimo (2021-09-14 13:28)

ardimo
Member | 5
+
0
-

Maybe a Diagram and some code I made could help here.
The Diagram is dumped down, so only the relevant Columns are shown:

                                                                     ┌───────────────────┐
                                                                     │TreeData           │
                                                                     ├───────────────────┤
                                                                     │TreeData (Primary) │
                                                                 ┌──►│Tree               │
┌─────────────────────────┐       ┌────────────────────────┐     │   │Name               │
│Content                  │       │Tree_Container          │     │   │                   │
├─────────────────────────┤       ├────────────────────────┤     │   └───────────────────┘
│Content (Primary)        │       │Tree_Container (Primary)│     │
│Container ───────────────┼──────►│Container               │     │   ┌───────────────────┐
│...                      │       │Tree ───────────────────┼─────┤   │TreePath           │
│                         │       │                        │     │   ├───────────────────┤
│                         │       │                        │     │   │TreePath (Primary) │
└─────────────────────────┘       └────────────────────────┘     └──►│Tree               │
                                                                     │Parent             │
                                                                     │                   │
                                                                     └───────────────────┘

I have set up a basic helper class to wrap all that database and query stuff, but the core is the function that assembles a query based on some arguments:

private function assembleBaseQuery(
  $treenode  = null,
  $recursive = true,
  $offset    = 0,
  $maxshow   = 20,
  $order     = null,
  $layouts   = array(),
  $distinct  = false
) {
  $table = $this->database_explorer->table('Content');

  # Base select with possible distinct option
  if ($distinct) {
    $table->select('DISTINCT ' . $this->getResultColumns());
  } else {
    $table->select($this->getResultColumns());
  }

  # Filter by layouts
  $table->where("Content.Layout", $this->prepareValuesForQuery($layouts));

  # Filter by tree
  $table_tree_container = $this->database_explorer->table('Tree_Container');
  if ($recursive) {
    # old subquery: "SELECT Container FROM Tree_Container INNER JOIN TreePath ON TreePath.Tree = Tree_Container.Tree WHERE TreePath.Parent = 123456"
    $table_tree_container->select('Container')->where('TreePath.Parent', $this->prepareValuesForQuery($treenode));
  } else {
    # old subquery: "SELECT Container FROM Tree_Container WHERE Tree_Container.Tree = 123456"
    $table_tree_container->select('Tree_Container.Container')->where('Tree_Container.Tree', $this->prepareValuesForQuery($treenode));
  }

  $table->where("Content.Container", $table_tree_container);

  # Limit and offset
  if ($offset > 0 or $maxshow > 0) {
    $table->limit($maxshow, $offset);
  }

  # Sort / order
  if ($order) {
    $table->order($order);
  } else {
    $table->order('datum DESC');
  }

  return $table;
}

My problems lay within the “Filter by tree” part – to be precise with the “recursive” option enabled. Non recursive means i just query for Tree-IDs directly, which is just the ID “Tree” in the Table “Tree_Container” – so it results in a basic query.

But with “recursive” I need to include a second table. I dont have to traverse a tree here, because all Treenodes have theyr parent already within the table, so i can just query for that. The old code did make a Inner-Join here:
SELECT Container FROM Tree_Container INNER JOIN TreePath ON TreePath.Tree = Tree_Container.Tree WHERE TreePath.Parent = 123456

I was reading the Database Explorer Documentation (For example this part), but I dont get it how to tell Nette how to connect the two Tables “Tree_Container” and “TreePath” through the respective “Tree” Column in each of the two Tables.

I am a bit lost here, trying all sorts of stuff for days now …

May be related to this 4 year old (unfortunately unanswered) question.

So I discovered there is an alias() and joinWhere() function – both of them not really documented much. So a bit more text for the description would be super helpful.

Also i struggle to find information on what a “table chain” is and how they are composed. The word “chain” is not in the documentation despite obviously in use at some points. (At least i guess its a chain if I see a “:” in use within a select-function).

Last edited by ardimo (2021-09-20 10:18)