Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

sqlite n:m relationships (ORM)

posted by doccie 8 years ago

Hey Will,

I'm trying to populate a related table using ORM, but it keeps telling me my tables aren't in a n-to-many relationship.

SQL:

CREATE TABLE "edition_movies"(
  "id" INTEGER PRIMARY KEY NOT NULL,
  "edition_id" INTEGER NOT NULL,
  "movie_id" INTEGER NOT NULL,
  CONSTRAINT "fk_edition_movie_edition1"
    FOREIGN KEY("edition_id")
    REFERENCES "edition"("id"),
  CONSTRAINT "fk_edition_movie_movie1"
    FOREIGN KEY("movie_id")
    REFERENCES "movie"("id")
);
CREATE TABLE "editions"(
  "id" INTEGER PRIMARY KEY NOT NULL,
  "screen_date" TIMESTAMP NOT NULL,
  "voting_start_date" TIMESTAMP NOT NULL,
  "voting_end_date" TIMESTAMP NOT NULL,
  "created_on" TIMESTAMP NOT NULL
);
CREATE TABLE "movies"(
  "id" INTEGER PRIMARY KEY NOT NULL,
  "title" VARCHAR(255) NOT NULL,
  "description" TEXT NOT NULL,
  "poster_path" VARCHAR(255) NOT NULL,
  "trailer" VARCHAR(255) NOT NULL DEFAULT 'youtube url',
  "cineart" VARCHAR(255) DEFAULT 'cinart url',
  "imdb" VARCHAR(255) DEFAULT 'imdb url',
  "created_on" TIMESTAMP NOT NULL
);

Using the following (generated) HTML form:

	<fieldset>
		<p> 
			<label>Screen date</label> 
			<input class="text-input medium-input datepicker" type="text" id="medium-input" name="screen_date" value="" />
		</p>
		<p> 
		    
			<label>Voting start date</label> 
			<input class="text-input medium-input datepicker" type="text" id="medium-input" name="voting_start_date" value="" />
		</p> 
		<p> 
			<label>Voting end date</label> 
			<input class="text-input medium-input datepicker" type="text" id="medium-input" name="voting_end_date" value="" />
		</p> 
                <p>
                        <label>First movie</label> 
			<select name="editionmovies">
	<option name="editionmovies::movie_id[0]" value="1">In A Better World</option>
	<option name="editionmovies::movie_id[0]" value="2">Dancing Dreams - Sur les pas de Pina Bausch (Tanztrame)</option>
	<option name="editionmovies::movie_id[0]" value="3">Une Sparation (A Separation)</option>
</select>
<input type="hidden" name="editionmovies::id[0]" value="" />
<input type="hidden" name="editionmovies::edition_id[0]" value="" />
                </p>
		<p>
                        <label>Second movie</label> 
			<select name="editionmovies">
	<option name="editionmovies::movie_id[1]" value="1">In A Better World</option>
	<option name="editionmovies::movie_id[1]" value="2">Dancing Dreams - Sur les pas de Pina Bausch (Tanztrame)</option>
	<option name="editionmovies::movie_id[1]" value="3">Une Sparation (A Separation)</option>
</select>
<input type="hidden" name="editionmovies::id[1]" value="" />
<input type="hidden" name="editionmovies::edition_id[1]" value="" />
                </p>
		<p> 
			<input class="button" type="submit" value="Insert edition" />&nbsp;<input class="button" type="button" value="Cancel" onClick="window.location = '/admin/editions/manage';" />
		</p>
	</fieldset> 

And this piece of PHP code:

$edition = new Edition();
$edition->populate();
$edition->populateEditionMovie();
$edition->store();

Both the EditionMovie and Edition classes are of course subclasses of fActiveRecord.

This is the error being thrown:

{doc_root}/inc/controller/admin/editions.php(20): Edition::autoInsertEdition()
{doc_root}/inc/dal/Edition.php(23): Edition->populateEditionMovie()
{doc_root}/inc/dal/Edition.php(23): fActiveRecord->__call('populateEdition...', Array)
{doc_root}/lib/flourish/fActiveRecord.php(1010): fActiveRecord::determineSubject('Edition', 'EditionMovie', NULL)
{doc_root}/lib/flourish/fActiveRecord.php(624)
The table editions is not in a *-to-manyrelationship with the table edition_movies

Any idea what might be causing this error to be thrown?

I believe that fSchema is failing because you are explicitly naming your foreign key constraints like CONSTRAINT "fk_edition_movie_movie1". For the short term you can try removing those.

Please do open a ticket so I don't forget to fix this!

posted by wbond 8 years ago

Thanks for the reply! I've added the ticket and will see if that solves the issue.

posted by doccie 8 years ago

doccie

Here is an ORM that works with Sqlite https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

posted by asavasamuel 6 years ago