• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

List styling and ordering with on the fly calc

m6xmed5

Member
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.
 
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).
 
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.
 
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)
 
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:
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.
 
No need for a join, just do a dependent subquery for the count ...

Code:
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
 
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.
 
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.
 
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:
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?
 
It's a bit clunky, but you'd have to redo the subquery in the stock_level field...

Code:
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
 
Thanks Hugh, I had been toying with this...
Code:
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.
 
It's a bit clunky, but you'd have to redo the subquery in the stock_level field...

Code:
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
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
 
I removed the comma after 'AS stock_level' because it was throwing a syntax error with it in.

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

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top