Loading ...
Sorry, an error occurred while loading the content.

Re: Multi-table scenarios

Expand Messages
  • tacoman_cool
    Jon, Hope it works. Would be interested in your final solution. It is very likely that any speed hit in doing the queries is going to freeze the UI. What you
    Message 1 of 4 , Jan 12, 2012
    • 0 Attachment
      Jon,

      Hope it works. Would be interested in your final solution.

      It is very likely that any speed hit in doing the queries is going to freeze the UI. What you may consider is using callback() to do your searching and rendering the background and then push it to the interface once you are done.

      Ian


      --- In taffydb@yahoogroups.com, "jonjoffy" <jon@...> wrote:
      >
      > Hi Ian, thanks for taking the time to respond, that helps loads.
      >
      > I like your idea and I'll try that and see what performance is like. I'm slightly worried on bigger tables there might be an overhead performing the FK lookups (if the underlying tables are quite big), but I could work out that by paginating the results on the client (to reduce the number of lookups needed). I could also try denormalising the tables slightly to reduce the number of tables (and lookups) that I need to do to present some of the views in the app.
      >
      > All in all, very impressed so far. Thank you.
      >
      > Jon
      >
      > --- In taffydb@yahoogroups.com, taffydb-owner@yahoogroups.com wrote:
      > >
      > >
      > > Hey Jon,
      > >
      > > Good questions. Currently there is no support for joins in TaffyDB. This is largely because it would require a pretty complex API for a feature that really isn't ideal to try and do in JavaScript, at least with a lot of layers of abstraction.
      > >
      > > That said, I think TaffyDB may work for you. Here is what I would do:
      > >
      > > Assuming each table is now a TAFFY object, let's add a "printer" function to every order item to create your grid with. Additionally updates to the order tables will be available to the printer function.
      > >
      > > OrderItems().update(function () {
      > > var orderItem = this;
      > > var order = Orders({OrderID:this.orderID});
      > > var product = Products({ProductID:this.ProductID});
      > > var manufacturer = Manufacturers({ManufacturerID:product.ManufacturerID});
      > > var customer = Customers({CustomerID:order.CustomerID});
      > >
      > > this.printer = function () {
      > > return orderItem.Quantity + " " + order.first().OrderDate + " " + product.first().ProductName + " " + manufacturer.first().ManufacturerName + " " + customer.first().CustomerName;
      > > }
      > > return this;
      > > });
      > >
      > > If I have my code right then you can just called .printer() on your items.
      > >
      > > For example:
      > >
      > > var results = [];
      > > OrderItems().each(function (r) {
      > > results[results.length] = r.printer();
      > > })
      > > var results = results.join("<br>");
      > >
      > > This may need a bit of adjustment but in theory it is fairly fast. TaffyDB will cache queries so when you look up a manufacturer or a customer it gets much faster the second time you do it. Likewise it will only run a query for data from one of the subqueries if the data in the table has changed.
      > >
      > > Hope that helps,
      > >
      > > Ian
      > >
      > >
      > >
      > >
      > >
      > > --- In taffydb@yahoogroups.com, "jonjoffy" <jon@> wrote:
      > > >
      > > > I'm in the process of evaluating alternate technologies for a legacy app which runs on Windows Mobile and uses SQL Server CE. The objective is to migrate the app to a HTML5 & PhoneGap solution, plus a JS/localStorage database (such as Taffy).
      > > >
      > > > I've been playing around with Taffy for the past few weeks, and I really like what I've seen so far, and congratulations on a great job. I really like the query syntax and the way it works.
      > > >
      > > > The only thing I'm unsure about with Taffy regards its suitability for this project (and it might be my misunderstanding with how it works), is how to store multiple tables on the client, and perform relational operations, such as joins across the tables.
      > > >
      > > > The application I'm migrating consists of around 10 tables, and the app needs to perform joins across these tables to display summary information on the UI. (The app also needs to synchronise these tables with the server, but that's done outside of Taffy).
      > > >
      > > > Let's say we have these fictional tables and I want to show the order history for a particular customer:
      > > >
      > > >
      > > > Orders
      > > > -------
      > > > OrderID
      > > > OrderDate
      > > > CustomerID
      > > >
      > > > OrderItems
      > > > ----------
      > > > OrderItemID
      > > > OrderID
      > > > ProductID
      > > > Quantity
      > > >
      > > > Products
      > > > --------
      > > > ProductID
      > > > ProductName
      > > > ManufacturerID
      > > >
      > > > Manufacturers
      > > > --------
      > > > ManufacturerID
      > > > ManufacturerName
      > > >
      > > > Customers
      > > > ---------
      > > > CustomerID
      > > > CustomerName
      > > > AddressID
      > > >
      > > >
      > > > So far, I've gone down the route of having multiple instances of Taffy, with each instance of Taffy representing a single table. However, to then display 'joined' query results across these 'tables', I'd need to perform three or four queries on each row (e.g find one customer, then for *each* order row, perform a query to find the order items, then for each order item row find the product, then for each product find the manufacturer!). If you want to display a list of twenty items on the UI, and each item has to show information from 5 tables, this approach could be quite slow. (I think I must be missing something here, and there must be a different way of doing things.)
      > > >
      > > > I could sync a denormalised set of data to the client, but it would dramatically increase the amount of data going over the wire and it's important to keep the data transfer as small as possible as some people are billed for usage, and also if there is poor network coverage it needs to be as fast as possible. Also, storing the tables separately makes synchronisation more efficient as the tables can be sync'd individually & incrementally.
      > > >
      > > > The docs don't seem to cover a multi-table scenario, so I'm not even sure if I should even be attempting this with Taffy, or if I need to be thinking differently.
      > > >
      > > > Jon
      > > >
      > >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.