How to build a dynamic table for treeview

[Ul]

Hello,

We are a small startup producing organic food. I want to use Tryton in a
for inventory management and later for accounting.

In food and especially organic food it is essential to exactly track all
the lots.

In the stock.moves table is all the information needed, but i need a way
to display the history of a specific lot in the sense of what lots were
used to produce it and what lots were used to produce them and so on
until the shipment the ingredients came with.

So it is going to be a big tree, that is not possible to know before.
After quite a lot of reading, i see three possibilities to approach the
problem:

1. owerwriting table_querry() in a new ModelSQL:

i could join several tables to have all relevant information in one
model, but i have to filter the records in steps and in each step
finding the parents of the curent lot, and only then i can start finding
the grandparents. I don't know how to filter the table with in such a
way, witch is (i think so) not possible with a Domain or a where-clause.

2.overwriting read() in a new ModelStorag:

i was reading about this possibility in the mailing-list
(https://www.mail-archive.com/try...@googlegroups.com/msg06049.html).
This way I can build the list of records as i need it. But read() gets
ids as an argument, and i don't know were the ids come from. And before
the read() is called and the tree is build, it is impossible to know how
many records will be there.

Is it possible to use the read() method anyway ignoring ids?

3. using babi or hebi

could perhaps solve my problem. But these are quite complex modules
with little documentation, so it would be nice to get a hit from
insiders whether it is worth diving into (and witch of them) to get a
solution.

This is what i found so far, but perhaps there other possibilities i
don't know of?

Regards and Thanks for your help,

Ulrich

[Cédric Krier]

On 2017-02-07 15:21, Ul wrote:
> Hello,
>
> We are a small startup producing organic food. I want to use Tryton in a
> for inventory management and later for accounting.
>
> In food and especially organic food it is essential to exactly track all
> the lots.
>
> In the stock.moves table is all the information needed, but i need a way
> to display the history of a specific lot in the sense of what lots were
> used to produce it and what lots were used to produce them and so on
> until the shipment the ingredients came with.
>
> So it is going to be a big tree, that is not possible to know before.

As far as I see it is just two trees on lot based each one on a
Many2Many using the production moves in a table query.
So it is about to show in a Many2Many the lot used in the output moves
of production which uses in the input moves the lot.
I think such Model can be written using a ModelSQL.table_query quite
easily. Such development could be included in the base of Tryton.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Ul]

Am 07.02.2017 um 15:59 schrieb Cédric Krier:
> On 2017-02-07 15:21, Ul wrote:
>>
>> So it is going to be a big tree, that is not possible to know before.
>
> As far as I see it is just two trees on lot based each one on a
> Many2Many using the production moves in a table query.
> So it is about to show in a Many2Many the lot used in the output moves
> of production which uses in the input moves the lot.
> I think such Model can be written using a ModelSQL.table_query quite
> easily.

With a ModelSQL.table_query i can combine the necessary tables basically
the stock.move and the stock.lot. It would be nice to pull in Infos from
ShipmentIn and from Production, like the effective date or the supplier,
but that isn't that important.
I would add fields.Function as 'parent' and 'childs', that it is
displayed as a tree not a plain list.

I see the main Problem in filtering the records to decide witch records
to show because they belong in this 'in-heritage'-tree.
My approch would be a loop starting at the 'root'-lot looking up the
lots that went into the production that produced the root-lot. and doing
the same with the lots in the result and again with this results until
every lot in the tree is processed that came out of a production.

here is an example how i imagine the output, of course with some more
columns like product, production-date and so on:

XY
AB
GH
JK
KL
WE
UI
CD
WE
NM

Such development could be included in the base of Tryton.
>

What could be included in the base? a filter to filter a table for
multi-stage relations? Perhaps it could be a special kind of domain
operator...
That would be nice, but i think I'm not able to do it myself or pay it
right now...

[Cédric Krier]

On 2017-02-07 18:22, Ul wrote:
> Am 07.02.2017 um 15:59 schrieb Cédric Krier:
> > On 2017-02-07 15:21, Ul wrote:
> >>
> >> So it is going to be a big tree, that is not possible to know before.
> >
> > As far as I see it is just two trees on lot based each one on a
> > Many2Many using the production moves in a table query.
> > So it is about to show in a Many2Many the lot used in the output moves
> > of production which uses in the input moves the lot.
> > I think such Model can be written using a ModelSQL.table_query quite
> > easily.
>
>
> With a ModelSQL.table_query i can combine the necessary tables basically
> the stock.move and the stock.lot. It would be nice to pull in Infos from
> ShipmentIn and from Production, like the effective date or the supplier,
> but that isn't that important.
> I would add fields.Function as 'parent' and 'childs', that it is
> displayed as a tree not a plain list.

I do not understand why you will need any Function fields.
You just have to create a Many2Many query table.

> I see the main Problem in filtering the records to decide witch records
> to show because they belong in this 'in-heritage'-tree.
> My approch would be a loop starting at the 'root'-lot looking up the
> lots that went into the production that produced the root-lot. and doing
> the same with the lots in the result and again with this results until
> every lot in the tree is processed that came out of a production.

I do not understand what is this "filtering" problem.

> here is an example how i imagine the output, of course with some more
> columns like product, production-date and so on:
>
> XY
> AB
> GH
> JK
> KL
> WE
> UI
> CD
> WE
> NM

I do not think it is possible to add random data as column.
But instead it will just need to have a relate action on the lot that
opens all stock moves using the selected lot.

> Such development could be included in the base of Tryton.
> >
>
> What could be included in the base? a filter to filter a table for
> multi-stage relations? Perhaps it could be a special kind of domain
> operator...

I do not understand what is this special kind of domain.

What I'm talking is that the down/up tree of lots should be a base
feature.

[Raimon Esteve]

hie

El dia 07/02/2017 15.30, "Ul" <uh...@gmx.de> va escriure:

See babi_* modules that add some default reports to know how to do new/custom reports.

This is what i found so far, but perhaps there other possibilities i
don't know of?

Regards and Thanks for your help,

Ulrich

--
You received this message because you are subscribed to the Google Groups "tryton" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/tryton/f26d3699-a91e-9c6d-2f74-67af5b65cd17%40gmx.de.

[Ul]

Am 07.02.2017 um 18:57 schrieb Cédric Krier:

> On 2017-02-07 18:22, Ul wrote:
>>
>> With a ModelSQL.table_query i can combine the necessary tables basically
>> the stock.move and the stock.lot. It would be nice to pull in Infos from
>> ShipmentIn and from Production, like the effective date or the supplier,
>> but that isn't that important.
>> I would add fields.Function as 'parent' and 'childs', that it is
>> displayed as a tree not a plain list.
>
> I do not understand why you will need any Function fields.
> You just have to create a Many2Many query table.

They were meant for the client. As i understood this is the marker that
tells the client to display the records as a tree not a plain list.

>
>> I see the main Problem in filtering the records to decide witch records
>> to show because they belong in this 'in-heritage'-tree.
>> My approch would be a loop starting at the 'root'-lot looking up the
>> lots that went into the production that produced the root-lot. and doing
>> the same with the lots in the result and again with this results until
>> every lot in the tree is processed that came out of a production.
>
> I do not understand what is this "filtering" problem.

If I do a SQL-Join with lot and move or if i do join two lots in the
manytomany table (that doesn't exist yet), i get infos about all the
lots, not just the Lots that are related to my starting lot. The next
step is to find the records of interest, and I don't know how to achieve
that with the tools i know by now...

>
>> here is an example how i imagine the output, of course with some more
>> columns like product, production-date and so on:
>>
>> XY
>> AB
>> GH
>> JK
>> KL
>> WE
>> UI
>> CD
>> WE
>> NM
>
> I do not think it is possible to add random data as column.

What do you mean with random data? The Letters were meant to symbolize
Lot-Numbers.

>
> What I'm talking is that the down/up tree of lots should be a base
> feature.
>

Perhaps i was thinking too narrow, if you implement such an up/down tree
based on a many to many relation in the base of tryton, than are many of
my points obsolete. I just tried to work with the possibilities that i
already know of and look how far i get with them.

Building a new base feature sound's like a quite some time till it is
usable. So there is the question if i would need a quick-and-dirty
solution in between.

[Ul]

Am 07.02.2017 um 18:57 schrieb Cédric Krier:

> On 2017-02-07 18:22, Ul wrote:
>
>> I see the main Problem in filtering the records to decide witch records
>> to show because they belong in this 'in-heritage'-tree.
>> My approch would be a loop starting at the 'root'-lot looking up the
>> lots that went into the production that produced the root-lot. and doing
>> the same with the lots in the result and again with this results until
>> every lot in the tree is processed that came out of a production.
>
> I do not understand what is this "filtering" problem.
>

The reason why I did see this problem with filtering was that I have
only very basic knowledge of SQL and I underestimated it's power.
After you stating that there is no problem I was reading more about SQL
and finally came across WITH RECURSIVE, that should do what i need.

Having up/down trees in the base would be a nice feature, but at the
moment i don't have the capacities to participate in development. But of
course you can get the code i write for my module if it helps.

After all I'm not a experienced programmer, just learning by doing
whatever I need.

[Ul]

Am 09.02.2017 um 10:27 schrieb Ul:
> Am 07.02.2017 um 18:57 schrieb Cédric Krier:
>>
>> I do not understand what is this "filtering" problem.
>>
> The reason why I did see this problem with filtering was that I have
> only very basic knowledge of SQL and I underestimated it's power.
> After you stating that there is no problem I was reading more about SQL
> and finally came across WITH RECURSIVE, that should do what i need.
>

Finally i have my tree out of a Many2Many Table using ModelSQL and
overriding table_querry() using WITH RECURSIVE.

For usual data it works fine.
But it doesn't work with a tree were one leaf or knot shows up two times
at 2 different places.
in this Case I get an Error in the Client: "You try to read records that
don't exist anymore."

I located the place were the error is triggered:
model/modelsql.py line 704 there is a check:
"if not len(fetchall) == len({}.fromkeys(sub_ids)):"
If i comment out the whole if clause or just the line calling the error
(line 717) it works fine.

How important is this check? How much do i risk if i leave this
error-call disabeled?

[Sergi Almacellas Abellana]

El 13/02/17 a les 21:57, Ul ha escrit:

This check says that you requested data for X records but the query only
returned Y. Normally it is used to ensure that the user is allowed to
read all the records he is trying to access. But I believe that in your
case there is something in your sql query that prevents to return the
correct data.

Do you mind sharing the query? So we investigate further which is
causing the error.



--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

[Ul]

Am 14.02.2017 um 09:30 schrieb Sergi Almacellas Abellana:
> El 13/02/17 a les 21:57, Ul ha escrit:
>>

>> For usual data it works fine.
>> But it doesn't work with a tree were one leaf or knot shows up two times
>> at 2 different places.
>> in this Case I get an Error in the Client: "You try to read records that
>> don't exist anymore."
>>
>> I located the place were the error is triggered:
>> model/modelsql.py line 704 there is a check:
>> "if not len(fetchall) == len({}.fromkeys(sub_ids)):"
>> If i comment out the whole if clause or just the line calling the error
>> (line 717) it works fine.
>>
>> How important is this check? How much do i risk if i leave this
>> error-call disabeled?
>>
>
>
> This check says that you requested data for X records but the query only
> returned Y. Normally it is used to ensure that the user is allowed to
> read all the records he is trying to access. But I believe that in your
> case there is something in your sql query that prevents to return the
> correct data.
>
> Do you mind sharing the query? So we investigate further which is
> causing the error.
>
>
>

My query is:

WITH RECURSIVE "a"("id", "parent", "origin", "create_uid", "product",
"create_date", "write_date", "write_uid", "lot", "rel_id",
"lot_id") AS (
SELECT 0, -1, "b"."origin" AS "origin", "b"."create_uid" AS
"create_uid", "b"."product" AS "product", "b"."create_date" AS
"create_date", "b"."write_date" AS "write_date",
"b"."write_uid" AS "write_uid", "b"."id" AS "lot", "c"."id" AS
"rel_id", "b"."id" AS "lot_id"
FROM "stock_lot" AS "b"
LEFT JOIN "stock_lot_relation" AS "c"
ON ("b"."id" = "c"."from_lot")
WHERE ("b"."id" = 10)
UNION
SELECT "c"."id", "a"."id", "b"."origin" AS "origin", "b"."create_uid"
AS "create_uid", "b"."product" AS "product", "b"."create_date"
AS "create_date", "b"."write_date" AS "write_date",
"b"."write_uid" AS "write_uid", "b"."id" AS "lot", "c"."id" AS
"rel_id", "b"."id" AS "lot_id"
FROM "stock_lot" AS "b"
LEFT JOIN "stock_lot_relation" AS "c"
ON ("b"."id" = "c"."from_lot"),
"a" AS "a"
WHERE ("c"."to_lot" = "a"."lot_id")
)
SELECT "a".* FROM "a"

as i told, with a strait tree it works well, but if some lots show up at
different places in the tree, some records have to be displayed several
times.

here is an example tree:

Lot a.id relation.id lot.id parent

Lot11 0 - 10 -1
Lot10 7 7 9 0
Lot3 1 1 3 7
Lot1 11 11 1 1
Lot2 12 12 2 1
Lot6 2 2 5 7
Lot4 3 3 4 7
Lot7 4 4 6 7
Lot8 5 5 7 7
Lot3 8 8 3 0
Lot1 11 11 1 1
Lot2 12 12 2 1

so as Lot3 is used twice the lines with relation.id 11 and 12 show up
twice.

i placed a print command right after 'if' in model/modelsql.py line 704
that gives me different output depending on what level of the tree i unfold:
len(fetchall): 9 != len({}.fromkeys(sub_ids)): 7
len(fetchall): 4 != len({}.fromkeys(sub_ids)): 2

so i suppose in 'fetchall' are all lines that are returned by the
database and in the second clause every id can only show up once as it
is a dict.

so the problem is quite clear, the question is how to deal with it the
proper way.
One way that works is to comment out the error-message, but this could
be a problem in other cases this error was intended for. Could this be a
relevant risk?
The cleaner way would be to change the if clause, to only apply in the
intended cases. But i don't know how to catch all cases that it should
without knowing what was in mind creating the clause and understanding
the whole context. And i don't really have the time to dig much deeper
right now if it isn't important.

I hope that gives a good picture of the situation...

best Regards,
Ulrich

[Sergi Almacellas Abellana]

El 15/02/17 a les 15:29, Ul ha escrit:

Thanks for sharing,

How do you define the stock_lot_relation table?
I think this is the table that we can add to the main stock_lot module
and this table can be computed with a table query by joining the inputs
and the outputs of a production.

>
> as i told, with a strait tree it works well, but if some lots show up at
> different places in the tree, some records have to be displayed several
> times.
>
> here is an example tree:
>
> Lot a.id relation.id lot.id parent
>
> Lot11 0 - 10 -1
> Lot10 7 7 9 0
> Lot3 1 1 3 7
> Lot1 11 11 1 1
> Lot2 12 12 2 1
> Lot6 2 2 5 7
> Lot4 3 3 4 7
> Lot7 4 4 6 7
> Lot8 5 5 7 7
> Lot3 8 8 3 0
> Lot1 11 11 1 1
> Lot2 12 12 2 1
>
> so as Lot3 is used twice the lines with relation.id 11 and 12 show up
> twice.
>
> i placed a print command right after 'if' in model/modelsql.py line 704
> that gives me different output depending on what level of the tree i unfold:
> len(fetchall): 9 != len({}.fromkeys(sub_ids)): 7
> len(fetchall): 4 != len({}.fromkeys(sub_ids)): 2

The problem is that the same id is returned for multiple records. The id
field of every model must be unique (as it's used to identify the
records). Indeed, you developing a table_query you are supposed to take
care about it. My advice is to compute the record id by using the ids of
the composed lots.

I hope it helps.

[Ul]

Am 15.02.2017 um 15:52 schrieb Sergi Almacellas Abellana:
> El 15/02/17 a les 15:29, Ul ha escrit:
>

> Thanks for sharing,
>
> How do you define the stock_lot_relation table?

as a usual Table in the database:

class LotRelation(ModelSQL, ModelView):
"Lot Relation"
__name__ = 'stock.lot.relation'
_rec_name = 'from_lot'
from_lot = fields.Many2One('stock.lot', 'from Lot', required=True)
quantity = fields.Float("Quantity", required=True,
digits=(16, Eval('unit_digits', 2)), depends=['unit_digits'])
uom = fields.Many2One("product.uom", "Uom", required=True)
to_lot = fields.Many2One('stock.lot', 'to Lot', required=True)

> I think this is the table that we can add to the main stock_lot module
> and this table can be computed with a table query by joining the inputs
> and the outputs of a production.

If relations only originate from productons, this can be computed from
the production table. But i want to populate it from shipment in too,
because i want to create a new internal lot with every shipment and
relate it to the lot of the supplier.
This has two reasons:
- if we get several shipments from on supplier that have the same
lot-number because the supplier produced it at once, i want to be able
to track down our lot witch shipment it came with.
- some suppliers have incredibly long lot-numbers and it's annoying to
type them every time you use it.

>
>>
>> so as Lot3 is used twice the lines with relation.id 11 and 12 show up
>> twice.
>>
>

> The problem is that the same id is returned for multiple records. The id
> field of every model must be unique (as it's used to identify the
> records). Indeed, you developing a table_query you are supposed to take
> care about it. My advice is to compute the record id by using the ids of
> the composed lots.

The problem is, that it is literally the same rows in the joined table
that are returned twice by the query. using the lot id won't help
anything, Lot3 is used 2 times and all lines within it are exactly the
same.
The only way would be to have some kind of counter or sequence in the
with-clause. But i didn't find a function that could do that. Any other
way like adding the depth of recursion and the relation id could also
give duplicates.
And i don't see any value such an id would add except fulfilling the
rule that the ids of every returned line have to be unique. There is no
information added by giving different ids, its even information lost:
the information that it is the very same line in the originating table.

[Sergi Almacellas Abellana]

El 15/02/17 a les 16:50, Ul ha escrit:

Well, that will be also possible if you use a UNION query to join data
from productions and from shipments. Indeed all the data is in the stock
move table but it have to be joined in a different way (one using the
shipment field and the other using the production fields). Something like:

select output.id, input.lot as from_lot, output.lot as to_lot
FROM stock_move input
INNER JOIN stock_move output ON input.shipment = output.shipment and
input.product = output.product and input.to_location =
output.to_location and input.lot <> output.lot
UNION
select output.id, input.lot as from_lot, output.lot as to_lot
FROM stock_move input
INNER JOIN stock_move output ON input.production_input =
output.production_output and input.product = output.product and
input.to_location = output.to_location and input.lot <> output.lot

Does this work for you?

>>
>>>
>>> so as Lot3 is used twice the lines with relation.id 11 and 12 show up
>>> twice.
>>>
>>
>> The problem is that the same id is returned for multiple records. The id
>> field of every model must be unique (as it's used to identify the
>> records). Indeed, you developing a table_query you are supposed to take
>> care about it. My advice is to compute the record id by using the ids of
>> the composed lots.
> The problem is, that it is literally the same rows in the joined table
> that are returned twice by the query. using the lot id won't help
> anything, Lot3 is used 2 times and all lines within it are exactly the
> same.
> The only way would be to have some kind of counter or sequence in the
> with-clause. But i didn't find a function that could do that. Any other
> way like adding the depth of recursion and the relation id could also
> give duplicates.
> And i don't see any value such an id would add except fulfilling the
> rule that the ids of every returned line have to be unique. There is no
> information added by giving different ids, its even information lost:
> the information that it is the very same line in the originating table.
>

Indeed, instead of building the full tree, I'm wondering if it will be
enough to add a relate option to open the parent and children records in
a new window. This will provide the same information in a simpler way.

[Cédric Krier]

On 2017-02-16 09:24, Sergi Almacellas Abellana wrote:
> Indeed, instead of building the full tree, I'm wondering if it will be
> enough to add a relate option to open the parent and children records in a
> new window. This will provide the same information in a simpler way.

Indeed for me, the tree should not be build in a SQL query nor in
ModelSQL but with the UI using a view trees using a Many2Many field as
children (one for up drill and one for down drill).
If this feature is developed this way, I think it can be included in
base.

[Ul]

Am 16.02.2017 um 09:34 schrieb Cédric Krier:
> Indeed for me, the tree should not be build in a SQL query nor in
> ModelSQL but with the UI using a view trees using a Many2Many field as
> children (one for up drill and one for down drill).
> If this feature is developed this way, I think it can be included in
> base.
>

You are absolutly right.
My problem was, that until now i thought that it is necessary to have
fields called 'parent' and 'childs' in the model to get a tree, as it
was like that in all the examples that crossed my way. So i was always
trying to generate them.
Now finally i realized that all it needs is to fill "field_childs"-field
in the the ir.ui.view.

Well, at least i learned a lot on the way...

[Ul]

Am 16.02.2017 um 09:24 schrieb Sergi Almacellas Abellana:
>
> Well, that will be also possible if you use a UNION query to join data
> from productions and from shipments. Indeed all the data is in the stock
> move table but it have to be joined in a different way (one using the
> shipment field and the other using the production fields). Something like:
>
> select output.id, input.lot as from_lot, output.lot as to_lot
> FROM stock_move input
> INNER JOIN stock_move output ON input.shipment = output.shipment and
> input.product = output.product and input.to_location =
> output.to_location and input.lot <> output.lot
> UNION
> select output.id, input.lot as from_lot, output.lot as to_lot
> FROM stock_move input
> INNER JOIN stock_move output ON input.production_input =
> output.production_output and input.product = output.product and
> input.to_location = output.to_location and input.lot <> output.lot
>
> Does this work for you?
>

Yes, thanks. I first had to build a field for the supplier lot into
stock.move and had to populate it with test data. Now my query is even
simpler than your sugestion:

SELECT "a"."create_uid" AS "create_uid", "a"."create_date" AS
"create_date", "a"."write_uid" AS "write_uid", "a"."write_date"
AS "write_date", "a"."id" AS "id", "a"."uom" AS "uom",
"a"."quantity" AS "quantity", "a"."lot" AS "from_lot",
"b"."lot" AS "to_lot"
FROM "stock_move" AS "a" INNER JOIN "stock_move" AS "b"
ON ("a"."production_input" = "b"."production_output")
WHERE (("a"."lot" > %s) AND ("b"."lot" > %s))
UNION
SELECT "a"."create_uid" AS "create_uid", "a"."create_date" AS
"create_date", "a"."write_uid" AS "write_uid", "a"."write_date"
AS "write_date", "a"."id" AS "id", "a"."uom" AS "uom",
"a"."quantity" AS "quantity", "a"."supplier_lot" AS "from_lot",
"a"."lot" AS "to_lot"
FROM "stock_move" AS "a"
WHERE (("a"."lot" > %s) AND ("a"."supplier_lot" > %s))

but i have to take care, that there is not more than one output with a
lot in every production or i will get double ids again.
Do you know how to generate new ids in a query?

Thanks...

[Sergi Almacellas Abellana]

El 17/02/17 a les 00:00, Ul ha escrit:

Indeed that's the hard part of this code. I'm wondering if you have
managed to solve it.

>
> Thanks...

[Ul]

Am 22.02.2017 um 09:39 schrieb Sergi Almacellas Abellana:
> El 17/02/17 a les 00:00, Ul ha escrit:

>> but i have to take care, that there is not more than one output with a
>> lot in every production or i will get double ids again.
>> Do you know how to generate new ids in a query?
>
> Indeed that's the hard part of this code. I'm wondering if you have
> managed to solve it.
>

In the first approach, i checked the output moves in validation of
production, allowing only one lot in the output of each production.

Now i calculate a new id from the ids of the input and output moves:
i shift the id of the input move 5 bits to the left, than i add the id
of the output move.
As the moves are idiomatically created, they should be in sequence and
at least within one production i think there can't be doubles. Having a
big database there still could theoretically be double ids, but i hope
it is rare enough to ignore it for now.
Of cause i would be happy if anyone finds a better method, or can
confirm that this method is reasonably save.

My query now looks like:
SELECT (("a"."id" << 5) + "b"."id") AS "id", "a"."create_uid" AS

"create_uid", "a"."create_date" AS "create_date",
"a"."write_uid" AS "write_uid", "a"."write_date" AS

"write_date", "a"."uom" AS "uom", "a"."quantity" AS "quantity",

"a"."lot" AS "from_lot", "b"."lot" AS "to_lot"
FROM "stock_move" AS "a"
INNER JOIN "stock_move" AS "b"
ON ("a"."production_input" = "b"."production_output")

WHERE (("a"."lot" > 0) AND ("b"."lot" > 0))
UNION
SELECT (("a"."id" << 5) + "a"."id") AS "id", "a"."create_uid" AS

"create_uid", "a"."create_date" AS "create_date", "a"."write_uid"

AS "write_uid", "a"."write_date" AS "write_date", "a"."uom" AS

"uom", "a"."quantity" AS "quantity", "a"."supplier_lot" AS
"from_lot", "a"."lot" AS "to_lot"
FROM "stock_move" AS "a"

WHERE (("a"."lot" > 0) AND ("a"."supplier_lot" > 0))


Regards,
Ulrich

[Cédric Krier]

On 2017-03-09 12:46, Ul wrote:
> My query now looks like:
> SELECT (("a"."id" << 5) + "b"."id") AS "id", "a"."create_uid" AS
> "create_uid", "a"."create_date" AS "create_date",
> "a"."write_uid" AS "write_uid", "a"."write_date" AS
> "write_date", "a"."uom" AS "uom", "a"."quantity" AS "quantity",
> "a"."lot" AS "from_lot", "b"."lot" AS "to_lot"
> FROM "stock_move" AS "a"
> INNER JOIN "stock_move" AS "b"
> ON ("a"."production_input" = "b"."production_output")
> WHERE (("a"."lot" > 0) AND ("b"."lot" > 0))
> UNION
> SELECT (("a"."id" << 5) + "a"."id") AS "id", "a"."create_uid" AS
> "create_uid", "a"."create_date" AS "create_date", "a"."write_uid"
> AS "write_uid", "a"."write_date" AS "write_date", "a"."uom" AS
> "uom", "a"."quantity" AS "quantity", "a"."supplier_lot" AS
> "from_lot", "a"."lot" AS "to_lot"
> FROM "stock_move" AS "a"
> WHERE (("a"."lot" > 0) AND ("a"."supplier_lot" > 0))

I still do not see the point to make an union.
Your first select could be just the table of a Many2Many relation and
you just show the moves as a tree using this Many2Many.
And of course you have the reverse Many2Many to go the other way.

For the unique id, it is pretty simple to generate unique id with fixed
number of ids, see model.Union.union_shard.

Also I do not see the point of the clause "lot > 0", if you want just
move with lot, use must use "lot IS NOT NULL". But I think it is good to
have the tree even if there is not lot.

[Ul]

Am 09.03.2017 um 13:17 schrieb Cédric Krier:
> On 2017-03-09 12:46, Ul wrote:
>> My query now looks like:
>> SELECT (("a"."id" << 5) + "b"."id") AS "id", "a"."create_uid" AS
>> "create_uid", "a"."create_date" AS "create_date",
>> "a"."write_uid" AS "write_uid", "a"."write_date" AS
>> "write_date", "a"."uom" AS "uom", "a"."quantity" AS "quantity",
>> "a"."lot" AS "from_lot", "b"."lot" AS "to_lot"
>> FROM "stock_move" AS "a"
>> INNER JOIN "stock_move" AS "b"
>> ON ("a"."production_input" = "b"."production_output")
>> WHERE (("a"."lot" > 0) AND ("b"."lot" > 0))
>> UNION
>> SELECT (("a"."id" << 5) + "a"."id") AS "id", "a"."create_uid" AS
>> "create_uid", "a"."create_date" AS "create_date", "a"."write_uid"
>> AS "write_uid", "a"."write_date" AS "write_date", "a"."uom" AS
>> "uom", "a"."quantity" AS "quantity", "a"."supplier_lot" AS
>> "from_lot", "a"."lot" AS "to_lot"
>> FROM "stock_move" AS "a"
>> WHERE (("a"."lot" > 0) AND ("a"."supplier_lot" > 0))
>
> I still do not see the point to make an union.
> Your first select could be just the table of a Many2Many relation and
> you just show the moves as a tree using this Many2Many.

If you just follow the lot relations made by production, you are right.
But as i explained to Sergi, i added a aditional field 'supplier_lot' to
move, that is filled by shipment in, as i do not want to use the Lot
numbers of the supplier internally, but of cause have to track my lots
back to them.

> And of course you have the reverse Many2Many to go the other way.

I did both directions with two different views on this table.

>
> For the unique id, it is pretty simple to generate unique id with fixed
> number of ids, see model.Union.union_shard.

I didn't know about yet. Can you tell me where it is used already, to
see an example?
My problem with double ids is not about the union, it is mainly from the
join in the first select-clause, so i don't know if this tool made for
union is solving it.

>
> Also I do not see the point of the clause "lot > 0", if you want just
> move with lot, use must use "lot IS NOT NULL".

when playing with the queries i once got an error because of wrong
datatype in python. Like this it worked.

> But I think it is good to
have the tree even if there is not lot.

I dont see the sense, as i want to see the relations of lots. having a
move without lot gives no information about a lot, but i need error
handling for missing attributes.

[Cédric Krier]

But this could be simply managed by adding the field supplier_lot on the
lot instead of adding a new field on the move.
With that, you stay standard and your view could be integrated in
Tryton.

> > For the unique id, it is pretty simple to generate unique id with fixed
> > number of ids, see model.Union.union_shard.
> I didn't know about yet. Can you tell me where it is used already, to
> see an example?
> My problem with double ids is not about the union, it is mainly from the
> join in the first select-clause, so i don't know if this tool made for
> union is solving it.

Yeps indeed UnionMixin will not work in this case.
Instead a pairing function should work: http://szudzik.com/ElegantPairing.pdf

> > But I think it is good to
> have the tree even if there is not lot.
> I dont see the sense, as i want to see the relations of lots. having a
> move without lot gives no information about a lot, but i need error
> handling for missing attributes.

If you have missing lot for some move, you will still be able to catch
later.

[Ul]

Am 09.03.2017 um 15:29 schrieb Cédric Krier:

> On 2017-03-09 14:23, Ul wrote:
>> If you just follow the lot relations made by production, you are right.
>> But as i explained to Sergi, i added a aditional field 'supplier_lot' to
>> move, that is filled by shipment in, as i do not want to use the Lot
>> numbers of the supplier internally, but of cause have to track my lots
>> back to them.
>
> But this could be simply managed by adding the field supplier_lot on the
> lot instead of adding a new field on the move.

this way i have the supplier Lot as a independent Lot that is nicely
integrated in my tree. for example: http://pasteboard.co/HpSrF4dk8.png
The lots with a party as origin are supplier lots, and with the context
menu i can change to the supplier or the shipment..

> With that, you stay standard and your view could be integrated in
> Tryton.
>

For integration in tryton it anyway has to be a separate module, as it
requires production module and stock_lot module. So why not using my
supplier_lot in move too. I can share my whole module (called
stock_lot_trace) if you are interested. But I'm afraid it does not yet
comply with your programming standards...

>>> For the unique id, it is pretty simple to generate unique id with fixed
>>> number of ids, see model.Union.union_shard.
>> I didn't know about yet. Can you tell me where it is used already, to
>> see an example?
>> My problem with double ids is not about the union, it is mainly from the
>> join in the first select-clause, so i don't know if this tool made for
>> union is solving it.
>
> Yeps indeed UnionMixin will not work in this case.
> Instead a pairing function should work: http://szudzik.com/ElegantPairing.pdf

I'm sorry, i'm not so fit in math and programming to understand that
deep enough to use it for my ids. I have to wait, until someone prepares
it further...

>
>>> But I think it is good to
>> have the tree even if there is not lot.
>> I dont see the sense, as i want to see the relations of lots. having a
>> move without lot gives no information about a lot, but i need error
>> handling for missing attributes.
>
> If you have missing lot for some move, you will still be able to catch
> later.

if you fill in a missing lot later, it will show up, as the query is
executed every time. But in my use-case there can be no missing lots, as
they are all set to required.
>

[Cédric Krier]

On 2017-03-09 18:45, Ul wrote:
> Am 09.03.2017 um 15:29 schrieb Cédric Krier:
> > On 2017-03-09 14:23, Ul wrote:
> >> If you just follow the lot relations made by production, you are right.
> >> But as i explained to Sergi, i added a aditional field 'supplier_lot' to
> >> move, that is filled by shipment in, as i do not want to use the Lot
> >> numbers of the supplier internally, but of cause have to track my lots
> >> back to them.
> >
> > But this could be simply managed by adding the field supplier_lot on the
> > lot instead of adding a new field on the move.
> this way i have the supplier Lot as a independent Lot that is nicely
> integrated in my tree. for example: http://pasteboard.co/HpSrF4dk8.png
> The lots with a party as origin are supplier lots, and with the context
> menu i can change to the supplier or the shipment..

I do not understand.
For me, there is no independent lot possible. Your internal lot should
always come from the same supplier lot. So the information about
supplier lot could be stored on the lot. But of course you can add a
Function field on stock move to show it next to the lot.

> > With that, you stay standard and your view could be integrated in
> > Tryton.
> >
> For integration in tryton it anyway has to be a separate module, as it
> requires production module and stock_lot module. So why not using my
> supplier_lot in move too. I can share my whole module (called
> stock_lot_trace) if you are interested. But I'm afraid it does not yet
> comply with your programming standards...

Of course, it is interesting to have production traceability.
But it must fit with the generic concept of Tryton. But for me, the need
of supplier_lot is not understood.

[Ul]

Am 09.03.2017 um 19:13 schrieb Cédric Krier:
> On 2017-03-09 18:45, Ul wrote:
>> Am 09.03.2017 um 15:29 schrieb Cédric Krier:
>>> On 2017-03-09 14:23, Ul wrote:
>>>> If you just follow the lot relations made by production, you are right.
>>>> But as i explained to Sergi, i added a aditional field 'supplier_lot' to
>>>> move, that is filled by shipment in, as i do not want to use the Lot
>>>> numbers of the supplier internally, but of cause have to track my lots
>>>> back to them.
>>>
>>> But this could be simply managed by adding the field supplier_lot on the
>>> lot instead of adding a new field on the move.
>> this way i have the supplier Lot as a independent Lot that is nicely
>> integrated in my tree. for example: http://pasteboard.co/HpSrF4dk8.png
>> The lots with a party as origin are supplier lots, and with the context
>> menu i can change to the supplier or the shipment..
>
> I do not understand.
> For me, there is no independent lot possible. Your internal lot should
> always come from the same supplier lot. So the information about
> supplier lot could be stored on the lot. But of course you can add a
> Function field on stock move to show it next to the lot.

Yes, the internal lot always comes from the same supplier lot. But
several supplier shipments can ship the same supplier lot. I want to
create a new internal lot at each shipment to track the internal lot
back to the shipment. This is the main reason to have an extra internal
lot. The other reason is, that some suppliers have lot numbers with more
than 15 digits and i don't want to type them all the time.
The supplier lot has to have a full line in the lot table to be
searchable as any other lot.

Yes, it would be possible with a many2one field in the internal lot. But
to include the relation in my tree i would need a function field in move
and i would still need a custom query with a union for the tree. So for
me it seems more complicated this way as i want to handle and display
this relation as the other relations.

[Cédric Krier]

On 2017-03-09 20:15, Ul wrote:
> Am 09.03.2017 um 19:13 schrieb Cédric Krier:
> > On 2017-03-09 18:45, Ul wrote:
> >> Am 09.03.2017 um 15:29 schrieb Cédric Krier:
> >>> On 2017-03-09 14:23, Ul wrote:
> >>>> If you just follow the lot relations made by production, you are right.
> >>>> But as i explained to Sergi, i added a aditional field 'supplier_lot' to
> >>>> move, that is filled by shipment in, as i do not want to use the Lot
> >>>> numbers of the supplier internally, but of cause have to track my lots
> >>>> back to them.
> >>>
> >>> But this could be simply managed by adding the field supplier_lot on the
> >>> lot instead of adding a new field on the move.
> >> this way i have the supplier Lot as a independent Lot that is nicely
> >> integrated in my tree. for example: http://pasteboard.co/HpSrF4dk8.png
> >> The lots with a party as origin are supplier lots, and with the context
> >> menu i can change to the supplier or the shipment..
> >
> > I do not understand.
> > For me, there is no independent lot possible. Your internal lot should
> > always come from the same supplier lot. So the information about
> > supplier lot could be stored on the lot. But of course you can add a
> > Function field on stock move to show it next to the lot.
> Yes, the internal lot always comes from the same supplier lot. But
> several supplier shipments can ship the same supplier lot.

That's not a problem if you store them on the lot, you can store many
times the same supplier lot. I even think we should have the same design
as for party identifier.

> I want to
> create a new internal lot at each shipment to track the internal lot
> back to the shipment. This is the main reason to have an extra internal
> lot.

I do not follow why this goal requires to add a new field.

> The other reason is, that some suppliers have lot numbers with more
> than 15 digits and i don't want to type them all the time.
> The supplier lot has to have a full line in the lot table to be
> searchable as any other lot.

I do not understand.

> Yes, it would be possible with a many2one field in the internal lot. But
> to include the relation in my tree i would need a function field in move
> and i would still need a custom query with a union for the tree. So for
> me it seems more complicated this way as i want to handle and display
> this relation as the other relations.

This is a simplification for the developer but for the user it is not as
he has to enter both lot on each move. Without talking about integrity
error, how will you prevent a user to encode on a move a lot and a wrong
supplier lot?

[Ul]

Am 10.03.2017 um 00:34 schrieb Cédric Krier:

> On 2017-03-09 20:15, Ul wrote:
>> The supplier lot has to have a full line in the lot table to be
>> searchable as any other lot.
>
> I do not understand.

In the beginning i i thought you just wanted to add a char field for the
supplier lot number. I just wanted to say this is not a option, it has
to be a relation to a separate lot.

>
>> Yes, it would be possible with a many2one field in the internal lot. But
>> to include the relation in my tree i would need a function field in move
>> and i would still need a custom query with a union for the tree. So for
>> me it seems more complicated this way as i want to handle and display
>> this relation as the other relations.
>
> This is a simplification for the developer but for the user it is not as
> he has to enter both lot on each move. Without talking about integrity
> error, how will you prevent a user to encode on a move a lot and a wrong
> supplier lot?

I hide the field for supplier_lot if the from_location is not of type
supplier. So it only shows up were it is relevant.
I think it is even easier for the user, because filling the form of
ShipmentIn, i directly have a field for supplier lot without opening the
internal lot. This is important because i want to automate the
generation of the internal lot in the next step by copying the data of
the supplier lot, replacing the number and origin. So i need to be able
to fill the supplier lot at shipment in before generating the internal lot.

On the other side, if i put the supplier lot into the internal lot it is
harder to hide it when it is irrelevant as for lots generated by
production. It is possible to show it depending on origin field, but the
field is not filled on generation as the creating shipment or production
sometimes don't have a id yet or it is only filled at saving.

Perhaps it's easiest to give you my code so you get the full picture:
https://owncloud.datnest.de/index.php/s/2UaeK68nPT7Q6KT

[Cédric Krier]

On 2017-03-10 12:10, Ul wrote:
> Am 10.03.2017 um 00:34 schrieb Cédric Krier:
> > This is a simplification for the developer but for the user it is not as
> > he has to enter both lot on each move. Without talking about integrity
> > error, how will you prevent a user to encode on a move a lot and a wrong
> > supplier lot?
> I hide the field for supplier_lot if the from_location is not of type
> supplier. So it only shows up were it is relevant.
> I think it is even easier for the user, because filling the form of
> ShipmentIn, i directly have a field for supplier lot without opening the
> internal lot. This is important because i want to automate the
> generation of the internal lot in the next step by copying the data of
> the supplier lot, replacing the number and origin. So i need to be able
> to fill the supplier lot at shipment in before generating the internal lot.

All of this can be achieved while still storing the information on the
lot.
Indeed when designing the database schema, the UI should never come in
the equation. The schema must be right according to the reality of the
modeling thing. And in this case, the internal lot number is linked to
the supplier lot number and both should follow all over the place. So it
is a wrong design to split them on the move because you can no more
guarantee this property.

> On the other side, if i put the supplier lot into the internal lot it is
> harder to hide it when it is irrelevant as for lots generated by
> production.

Why? You could have an external lot number for a production. For example
if the process is delegated to an external producer.

> It is possible to show it depending on origin field, but the
> field is not filled on generation as the creating shipment or production
> sometimes don't have a id yet or it is only filled at saving.
>
> Perhaps it's easiest to give you my code so you get the full picture:
> https://owncloud.datnest.de/index.php/s/2UaeK68nPT7Q6KT

I see that indeed for supplier lot, you even reuse the stock.lot. So for
me, it breaks the contract that lot should represent the same unique set
of products. Because you will have to entries in the stock.lot table for
the same set.

[Ul]

Am 10.03.2017 um 13:04 schrieb Cédric Krier:
>
> I see that indeed for supplier lot, you even reuse the stock.lot. So for
> me, it breaks the contract that lot should represent the same unique set
> of products. Because you will have to entries in the stock.lot table for
> the same set.
>

I do not think that these two entries are for the same set. In my
understanding the supplier lot is the set of goods that is under control
of the supplier, and the internal lot is the set of goods that is in my
control. So by the shipment the goods move out of one set and become a
new one that just origins from the supplier lot.

I see shipment similar like a step of processing. And it even can be, as
the goods are in an unknown environment while in transport.
For example i receive 75 kg of sugar of supplier lot A, perhaps they got
some temperature or moisture or something on the way. Something the last
shipment of 50 kg sugar of supplier lot A didn't. So these two batches
of sugar differ perhaps even enough to need different treatment. I would
say they are no longer in one set, as they can have different
properties. They just share their origin in supplier lot A. For me
shipmentIn is the logically perfect place to track this.