Please upgrade here. These earlier versions are no longer being updated and have security issues.

How to retrieve the currently defined column names in a garden table

rbrahmsonrbrahmson "You may say I'm a dreamer / But I'm not the only one"NY ✭✭✭

I am writing a plugin that filters on the values of several columns in the discussion table. I'd like to validate that the columns I specify in the dynamically constructed SQL are defined in the Discussions table. I've tried the following code snippet:

public function GetColumns($CheckField,$Table = 'Discussion') {
    echo '<br><h>'.__LINE__.' Table:'.$Table;       // generates: "271 Table:Discussion"
    echo '<br>'.__LINE__.' CheckField:'.$CheckField;    // generates: "272 CheckField:DiscussionID"
    $Sql = Gdn::sql()->fetchColumnSql($Table);
    echo '<br>'.__LINE__.' Sql:'.$Sql;      // generates: "274 Sql:show columns from ufce_Discussion"
    $Columns = array();
    $Columns = Gdn::sql()->query($Sql);     //This failes with the errors putlines below.

Unfortunately I get these errors (in the log file):

31 Mar 2016 - 22:14:50 intranet.com Garden Exception: PDO Statement failed to prepare|Gdn_Database|Query|You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID' at line 2 in /forum/srvr191/intr_html/Vanilla/library/database/class.database.php on 293
31 Mar 2016 - 22:14:50 intranet.com Garden #0 [internal function]: Gdn_ErrorHandler(256, 'PDO Statement f...', '/forum/srvr191...', 293, Array)
31 Mar 2016 - 22:14:50 intranet.com Garden #1 /forum/srvr191/intr_html/Vanilla/library/database/class.database.php(293): trigger_error('PDO Statement f...', 256)
31 Mar 2016 - 22:14:50 intranet.com Garden #2 /forum/srvr191/intr_html/Vanilla/library/database/class.sqldriver.php(1630): Gdn_Database->Query('select w.UserID...', Array, Array)
31 Mar 2016 - 22:14:50 intranet.com Garden #3 /forum/srvr191/intr_html/Vanilla/library/database/class.sqldriver.php(654): Gdn_SQLDriver->Query('select w.UserID...')
31 Mar 2016 - 22:14:50 intranet.com Garden #4 /forum/srvr191/intr_html/Vanilla/applications/vanilla/models/class.discussionmodel.php(348): Gdn_SQLDriver->Get()
31 Mar 2016 - 22:14:50 intranet.com Garden #5 /forum/srvr191/intr_html/Vanilla/applications/vanilla/controllers/class.discussionscontroller.php(136): DiscussionModel->GetWhere(false, 0, '100')
31 Mar 2016 - 22:14:50 intranet.com Garden #6 /forum/srvr191/intr_html/Vanilla/plugins/FilterDiscussion/class.FilterDiscussion.plugin.php(73): DiscussionsController->Index('p1')
.
.
.

From the little I understand it appears than my query for the columns of the discussion table are somehow associated with some other query ("...'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID' ...").

I'd appreciate some help here. Thanks in advance!

Comments

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    Take a look at the class.model.php method filterSchema()


    rbrahmson
  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    And I have learned today, that if you work with Gdn::sql() you work with an existing instance of the sql class, so you might have "relicts" from a former sql call or even worse, you destroy the sql that should be built before/after your custom method is called


    rbrahmson
  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    I'd say that the shortest "Vanilla way" to check that would be this:

    $schema = new Gdn_Schema($table);
    $fields = array_keys($schema->fields());
    if (!in_array($checkField, $fields)) {
        echo 'Cheater!';
        return;
    }
    


  • rbrahmsonrbrahmson "You may say I'm a dreamer / But I'm not the only one" NY ✭✭✭

    First, thanks for looking at this and trying to help! I was playing with some of the functions (e.g. trying to call filterchema). Nothing worked...

    Back to your suggested code - right of the bat the line "$Schema = new Gdn_Schema($Table);" generates an error:

    Garden Exception: PDO Statement failed to prepare|Gdn_Database|Query|You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID' at line 2 in .../Vanilla/library/database/class.database.php on 29301

    I sense that your previous message that what you wrote

    "I have learned today, that if you work with Gdn::sql() you work with an existing instance of the sql class, so you might have "relicts" from a former sql call or even worse, you destroy the sql that should be built before/after your custom method is called"

    is in play here. This is very disturbing. I hope I am wrong and that all I miss is to initiate some process (ergo the "failed to prepare" in the error message).

  • hgtonighthgtonight ∞ · New Moderator

    Get the schema from the model:

    $model = new DiscussionModel();
    $fields = $model->defineSchema()->fields();
    

    $fields is an array of all the columns in the model along with their attributes.

    Tested as working on Vanilla 2.2.

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • rbrahmsonrbrahmson "You may say I'm a dreamer / But I'm not the only one" NY ✭✭✭

    Thanks!
    I should have mentioned that I am running Vanilla Version 2.1.13. When I run the above code I get:

    PHP Fatal error: Call to a member function fields() on a non-object

    So I tried to break it into small steps:

    $model = new DiscussionModel();
    $schema = $model->defineSchema();  //This breaks with the error quoted below
    $fields = $schema->fields(); //Generates PHP Fatal error:  Call to a member function fields() on a non-object
    var_dump($fields);  //never got here
    

    PDO Statement failed to prepare|Gdn_Database|Query|You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID'

    I can't yet move to 2.2 but I'd like (if possible) to be able to code this for 2.1.13 as well as 2.2...

  • hgtonighthgtonight ∞ · New Moderator

    In 2.1, the Gdn_Model::DefineSchema method doesn't return the schema object. You will have to grab it after the definition:

    $model = new DiscussionModel();
    $model->DefineSchema();
    $schema = model->Schema;
    $fields = $schema->Fields();
    var_dump($fields);
    

    I didn't test it, because 2.1 should be replaced ASAP.

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

    R_J
  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    What I meant about the Gdn::sql and what I've learned... Try decho(Gdn::sql()); right at the beginning of your function. The context in that you call your function seems to be somewhere, where the sql is currently built up and not yet finished. So what you are trying to do interferes with the program flow where you call your function.

    You would need to get your own instance:

    $db = new Gdn_Database();
    $sql = $db->sql();
    

    or

    $sql = clone Gdn::sql();
    $sql->reset();
    

    I don't know which is to be preferred...


  • rbrahmsonrbrahmson "You may say I'm a dreamer / But I'm not the only one" NY ✭✭✭

    Thanks @hgtonight - that fails as well on line 2 of your sample code ($model->DefineSchema()), with the same error.

    PDO Statement failed to prepare|Gdn_Database|Query|You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID'

    I am fearful of moving to V2 when I read that some plugins are not compatible. For our departmental intranet I was taking a wait and see approach (less concerned about security because it is behind our firewall).

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    defineSchema() makes use of Gdn_Schema and Gdn_Schema makes use of Gdn::SQL(), if no database is provided.
    You have to create the schema and pass not only the name of the table, but also a new instance of the db. Then Gdn_Schema will use that db and doesn't interfere with the half built sql from your calling function.

    $database = new Gdn_Database();
    $schema = new Gdn_Schema($table, $database);
    $fields = array_keys($schema->fields());
    decho($fields);
    


    rbrahmson
  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    And this also works with the solution hgtonight mentioned:

    $model = new DiscussionModel();
    
    $database = new Gdn_Database();
    $model->Database = $database;
    
    $model->DefineSchema();
    $schema = $model->Schema;
    $fields = $schema->Fields();
    var_dump($fields);
    

    If you only need the fields, I would use the shorter version from my above post since it has not so much overhead and the "model way" essentially does the same.

    If you like to work further with the table, you might like to use the model creation.


    rbrahmson
  • rbrahmsonrbrahmson "You may say I'm a dreamer / But I'm not the only one" NY ✭✭✭

    Thank you @R_J and @hgtonight - It works perfectly now (and I also tried it on V2 test server I just installed).
    Not only have you solved my specific question but also helped me learn more about Vanilla. One day,in a very distant future (maybe on another Galaxy just behind the MVC123 warmhole), we could chat on the same level...

    As usual, this knowledge will percolate into plugins that I will upload to the plugin repository.

    hgtonight
Sign In or Register to comment.