<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">Hello,<div class=""><br class=""></div><div class="">How do you feel about database triggers?</div><div class=""><br class=""></div><div class="">Something like this should do what you're after, I think:</div><div class=""><br class=""></div><div class="">```</div><div class="">CREATE TRIGGER ensure_children_are_indexed_scoped_to_parent</div><div class=""> AFTER INSERT ON child_table</div><div class=""> UPDATE child_table SET index = MAX(index) + 1 WHERE id = LAST_INSERT_ID();</div><div class="">```</div><div class=""><div class=""><br class="webkit-block-placeholder"></div><div class="">No idea if it still works, but you could manage them using something like `hair_trigger`:</div><div class=""><br class=""></div><div class=""> <a href="https://github.com/jenseng/hair_trigger" class="">https://github.com/jenseng/hair_trigger</a></div><div class="">
<div style="color: rgb(0, 0, 0); letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><br class="">Yours,<br class="">Craig | <a href="http://barkingiguana.com/" class="">http://barkingiguana.com/</a><br class="">--<br class="">Barking Iguana Ltd. is a company registered in England and Wales.<br class="">Registered number: 08915147. Registered address: Jubilee House, East Beach, Lytham, St. Annes, Lancashire, England, FY8 5FT.</div>
</div>
<br class=""><div><blockquote type="cite" class=""><div class="">On 2 Sep 2016, at 10:11, Andrew Stewart <<a href="mailto:boss@airbladesoftware.com" class="">boss@airbladesoftware.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="">Hello LRUG!<br class=""><br class="">I have a Rails app using MySQL. In the app I have a parent model with children and I would like to number the children scoped to the parent – much as GitHub issues are numbered by repo.<br class=""><br class="">MySQL autoincrement sequences would be ideal if they could be scoped by parent but I don't think that's possible.<br class=""><br class="">For the past 8 years each parent model has held a counter. In an after_create callback in the child model, it calls a method on the parent which increments the counter and returns it; the child updates itself to store that value.<br class=""><br class="">This generally works but I've noticed I get the odd deadlock and uniqueness violation (there's a uniqueness constraint on the child for [parent_id, number]). These errors crop up when people are importing large numbers of children into the parent's collection.<br class=""><br class="">An alternative might be to change the after_create callback to query the table for the maximum number among the parent's children, add one, then update itself with that value. Then the parent table wouldn't be involved. But it still feels a bit hacky.<br class=""><br class="">This strikes me as the sort of thing for which there must be a standard solution. Any hints would be appreciated!<br class=""><br class="">Many thanks in advance,<br class=""><br class="">Andy Stewart<br class="">_______________________________________________<br class="">Chat mailing list<br class=""><a href="mailto:Chat@lists.lrug.org" class="">Chat@lists.lrug.org</a><br class="">Archives: http://lists.lrug.org/pipermail/chat-lrug.org<br class="">Manage your subscription: http://lists.lrug.org/options.cgi/chat-lrug.org<br class="">List info: http://lists.lrug.org/listinfo.cgi/chat-lrug.org<br class=""></div></div></blockquote></div><br class=""></div></body></html>