Problem with Nette ORM and custom convention

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”?

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.

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)