List styling and ordering with on the fly calc

Discussion in 'Standard Support' started by m6xmed5, Aug 3, 2018.

  1. m6xmed5

    m6xmed5 Member

    Level: Standard
    Hi guys, stuck with this one...

    I have a simple stock list with 3 elements 'part_number' (eval populated field) 'maximum_stock' (field) and 'units_in stock' (calc)

    The units_in_stock calc is calculated on the fly based on values in another table.

    Here is my working calc code...
    PHP:
    $db = FabrikWorker::getDbo();
    $query = $db->getQuery(true);
    $partnum ='{stock_levels___part_number}';
    $pwa = "pwa";
    $nyent = "Not Tested";




    $query->clear()
      ->select("COUNT(id)")
      ->from('automatic_transmission_jobs')
      ->where($db->quote($partnum) . ' = ' . $db->quoteName('automatic_transmission_jobs.part_number') . ' AND ' . $db->quoteName('automatic_transmission_jobs.test_result') . ' != ' . $db->quote($nyent) . ' AND ' . 'status !=' . $db->quote($pwa));


    $db->setQuery($query);
    $thepart = $db->loadResult();

    //var_dump((string)$query);


    if('{stock_levels___maximum_stock}' > $thepart AND $thepart > 0) {
    return($thepart . " Replace Stock");
    }

    else if('{stock_levels___maximum_stock}' <= $thepart){
    return($thepart . " OK");
    }

    else if($thepart == 0){
    return($thepart . " No stock left");
    }
     

    I want to pre-filter the list based on the calc element but the data is not saved to the database because the list is read only and the data in the field is dynamic as the list the data is taken from constantly changes.

    Is there any way to pre-filter and order and css style the list without the database entries?
    If not is there another way I can do this to achieve the results I'm after?
    I can't think of any way to do it.
     
  2. troester

    troester Well-Known Member Staff Member

    Level: Standard
    I would create a MySQL view and create a Fabrik list on this view.
    Then you can use prefilters, filters, ordering as usual (I think, I didn't test).
     
    m6xmed5 likes this.
  3. m6xmed5

    m6xmed5 Member

    Level: Standard
    That didn't even cross my mind, good idea!
    So I would use the table I have with 'part_number' and 'stock_maximum' only then export the list I have currently as a content type
    Then create a mysql view and use the content type with a connection to the mysql view as a database table for the list.

    I'll try that out.
     
  4. troester

    troester Well-Known Member Staff Member

    Level: Standard
    Not sure what you mean with content type, this is only for creating new lists with new database tables.

    The mySQL view is created in e.g. phpMyAdmin. Make sure you have some (dummy) column you can select as primary key in Fabrik (not really used, a view is readonly; but it's needed for the general structure).

    Then create a Fabrik list, in "Data" don't set a table name but select the view in "Database table", save, select the primary key in "Data" (if it was not detected automatically)
     
    m6xmed5 likes this.
  5. m6xmed5

    m6xmed5 Member

    Level: Standard
    I'm having a lot of trouble converting my query from the magic of fabrik into raw mysql.
    Could you give any pointers? Here's where I'm at so far...

    Code (Text):
    SELECT
    stock_levels.id AS id,
    stock_levels.part_number AS part_number,
      stock_levels.minimum_stock AS minimum_stock,
      stock_levels.maximum_stock AS maximum_stock,
      COUNT(automatic_transmission_jobs.part_number) AS in_stock
    FROM
      stock_levels
      left join automatic_transmission_jobs ON stock_levels.part_number = automatic_transmission_jobs.part_number
      AND automatic_transmission_jobs.test_result != "Not Tested"
      AND automatic_transmission_jobs.status != "pwa"
    It doesn't seem to be able to count for each row it only wants to count all records that match the 2 and clauses.
     
  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    No need for a join, just do a dependent subquery for the count ...

    Code (Text):

    SELECT
      stock_levels.id AS id,
      stock_levels.part_number AS part_number,
      stock_levels.minimum_stock AS minimum_stock,
      stock_levels.maximum_stock AS maximum_stock,
      (SELECT COUNT(*)
          FROM automatic_transmission_jobs
          WHERE part_number = stock_levels.part_number
          AND test_result != "Not Tested"
          AND status != "pwa"
       ) AS in_stock
    FROM
      stock_levels
     
    -- hugh
     
    m6xmed5 likes this.
  7. m6xmed5

    m6xmed5 Member

    Level: Standard
    Thanks Hugh,

    That did the trick. I have another question about prefilters. I'm not sure if its not working because its a view or because my prefilter is wrong but...

    '(elements) in stock -- (condition) less than -- (value) {$q->maximum_stock} -- (type) eval -- (access) public'

    returns no records even though the vast majority of the rows on the list have an in_stock value that is less than maximum stock.

    Any ideas? I was going to try and write a prefilter query but I'm not sure what the correct format is. I had a look at the wiki but couldn't make sense of it.
     
  8. m6xmed5

    m6xmed5 Member

    Level: Standard
    I just realized that what I want to do with the pre-filter cant be done, because the filter value one value for the entire column and not different for each row.
    So instead I need to add another column to the view to give me 'in_stock' - 'maximum_stock' and pre-filter that column < 0. Just need to figure out how to add that into the query for the view.
     
  9. m6xmed5

    m6xmed5 Member

    Level: Standard
    I'm kind of stuck with this again, I finally got a working query to return the results I wanted then MySQL responded with a swift kick up the backside.
    I can't have a sub-query after FROM in a create or replace view query. Here's what I have...
    Code (Text):
    CREATE
    OR REPLACE VIEW in_stock_levels AS
    SELECT
      *,
      `in_stock` - `maximum_stock` AS levels
    FROM(
        SELECT
          stock_levels.id AS id,
          stock_levels.part_number AS part_number,
          stock_levels.minimum_stock AS minimum_stock,
          stock_levels.maximum_stock AS maximum_stock,
          (
            SELECT
              COUNT(*)
            FROM
              automatic_transmission_jobs
            WHERE
              part_number = stock_levels.part_number
              AND test_result != "Not Tested"
              AND status != "pwa"
          ) AS in_stock
        FROM
          stock_levels
      ) AS check_stock
     
    I can't see how I can rewrite it to not have the sub-query after FROM.
    But I'm no MySQL genius any suggestions?
     
  10. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    It's a bit clunky, but you'd have to redo the subquery in the stock_level field...

    Code (Text):

    SELECT
          stock_levels.id AS id,
          stock_levels.part_number AS part_number,
          stock_levels.minimum_stock AS minimum_stock,
          stock_levels.maximum_stock AS maximum_stock,
          (
            SELECT
              COUNT(*)
            FROM
              automatic_transmission_jobs
            WHERE
              part_number = stock_levels.part_number
              AND test_result != "Not Tested"
              AND status != "pwa"
          ) AS in_stock,
          (
            SELECT
              COUNT(*)
            FROM
              automatic_transmission_jobs
            WHERE
              part_number = stock_levels.part_number
              AND test_result != "Not Tested"
              AND status != "pwa"
          ) - stock_levels.maximum_stock AS stock_level,
        FROM
          stock_levels
     
    The issue being that you can't use the 'in_stock' select in another field selection, as it's derived from a subquery.

    Shouldn't hurt performance, as MySQL is pretty good at spotting duplications like that, and doing keyhole optimizations so it ony runs that subquery one time for each row, not twice.

    -- hugh
     
    m6xmed5 likes this.
  11. m6xmed5

    m6xmed5 Member

    Level: Standard
    Thanks Hugh, I had been toying with this...
    Code (Text):
    SELECT
        stock_levels.id,
        stock_levels.part_number,
        stock_levels.minimum_stock,
        stock_levels.maximum_stock,
        COUNT(automatic_transmission_jobs.id) AS in_stock,
        'in_stock' - maximum_stock AS levels
    FROM stock_levels
    LEFT OUTER JOIN automatic_transmission_jobs USING (part_number)
    WHERE
        automatic_transmission_jobs.test_result != 'Not Tested'
        AND automatic_transmission_jobs.status !='pwa'
    GROUP BY stock_levels.id
    MySQL doesn't seem to be able to do anything with the result of the count, it just returns 0, so I end up with a levels column that has the negative value of the maximum_stock column, I'm going to try your version now.
     
  12. m6xmed5

    m6xmed5 Member

    Level: Standard
    Yup this works, thank you thats another one I owe you. I removed the comma after 'AS stock_level' because it was throwing a syntax error with it in.

    Marc
     
  13. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Oh yeah, sorry about that. I'd copied and pasted the original chunk, forgot to remove the comma.

    -- hugh
     

Share This Page